Hi All, I have a requirement to show on X-Axis Last Period of last Year plus all periods of current year … i.e.,Screenshot here
I already have a Column year period that is coming from the SQL server
I have a single selector slicer on Fiscal Year i.e., 2020,2021,2022, etc.,
so based on Year selected, I should show this X-Axis dynamically to display only the last period of the previous year and all periods of the current year.
EDIT :
So Far I am able to get the last period last year value dynamically based on year slicer selection.
CustomYearPeriod = VAR MonthN = If(dim_date[CalendarMonth] < 10, "0" & dim_date[CalendarMonth],dim_date[CalendarMonth])
VAR TargetYear = VALUE(dim_date[FiscalYear])
VAR PreviousYr = TargetYear-1
Var MaxPeriod = Max(dim_date[CalendarMonth])
VAR Result = CALCULATE(Min(dim_date[CalendarYearPeriod]),FILTER(dim_date,dim_date[CalendarYearPeriod] >=
PreviousYr &" P" & MaxPeriod))
Return Result
Hi @Archer. The PBIX you attached has only a Dates table (i.e., no data). Please create some sample data and the visual you’re looking to enhance, and the forum members can continue from there. Also, your date table should be marked as a date table for time intelligence DAX calculations to operate reliably.
Greg
Hello @Archer, to receive a resolution to your inquiry, please make sure that you provide all the necessary details on this thread.
We also noticed that no response has been received from you since July 3. If there won’t be any activity from your end in the coming days, we’ll be marking this thread as SOLVED.
One way to get what you want is to add a disconnected Fiscal Years table, then use it for your fiscal year slicer. Then, you can harvest the selected value from the slicer and compute your desired graph range
Graph Fiscal Period Max =
VAR _CurrentFiscalYear = SELECTEDVALUE( 'Fiscal Years'[Fiscal Year] )
VAR _Result = INT( _CurrentFiscalYear & "12")
RETURN
_Result
Graph Fiscal Period Min =
VAR _CurrentFiscalYear = SELECTEDVALUE( 'Fiscal Years'[Fiscal Year] )
VAR _Result = INT( ( INT( _CurrentFiscalYear) - 1 ) & "12")
RETURN
_Result
Then, you can develop and use measures in your visual rather than naked columns, using CALCULATE and your desired graph range like:
Actual =
CALCULATE( SUM( InventoryData[actual] ),
FILTER( dim_date,
dim_date[FiscalYearPeriod] >= [Graph Fiscal Period Min] &&
dim_date[FiscalYearPeriod] <= [Graph Fiscal Period Max]
)
)
You can then develop similar measures for Budget, Forecast, and Prior Year. Using these measures, you graph looks like:
Hi @Archer, did the response provided by @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
