Forum Discussion
Calculate based on Slicer Selection
Hi,
I have a file containing data consisting of actauls and several forecast versions, I'm building some time intelligence measures and would like the app to use the correct forecast at the current point in time the user selects to see the report for. The forecast naming convetion is "City Forecast YYYYQQ" so with the year and month in the name it should be possible to detect the forecast which was the current at the specific point in time the user would like to see it for. I've built a measure which takes the selected value of the year and month in the slicer which I use in the filter formula, problem seems to be the calcualte function evaluates and breaks the data down per month so that my full year estiamte based on the forecast is comingled with the different forecasts versions which were relevant at that point in time. Any clever minds out there who might have suggestions for how to solver this? below's the formula I use.
Hi y'all, thanks for your efforts but i sovled this the other day using variables.
E
10 Replies
Is it YYYYQQ or YYYYMM .
With YYYYMM
You can create A date like
Date = date(left([YYYYMM],4),right([YYYYMM],2)
Now you can use time intelligence With a date calendar
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Thanks for your reply,
I have a Master Calendar Lookup Table already which in addition to a Master Date field hosts both the YYYYMM and YYYYQQ fields, the lookup table is connected through the master date to a date field in my Financial Facts Table . The problem isn't with the time intelligence solutions, the problem lays with filtering the correct forecast version in the calculate() formula, it's supposed to detect the forecast version to use and filter the forecast versions column with the correct forecast at the point in time selected in the YYYY and MM slicers by the user.
I'll use the Oslo forecast here as an example, if the user selects in the YYYY and MM slicers to see our performance as of 2019 03 as the point in time then what was considered the current forecast at March -19 was "Oslo Forecast 2019Q1", but if the user selects in the slicers to see for 2019 08 then what was the current forecast at that point in time was "Oslo Forecast 2019Q3. These different forecast version are appended on top of each other in the data model and the naming convention which makes them unique live in the forecast versions column, so depending on the selected slicer value the calculate() function needs to detect what was the "current forecast" at that point in time, any idea how you can make the calculate() function evaluate and filter the Forecast Versions based on the selected values in the Year and Month slicers?
Thanks, E
