Calculate based on Slicer Selection

[2w]

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.

CALCULATE(CALCULATE(Fact,FILTER('Financial Data Facts','Financial Data Facts'[Forecast Scenario Description]=[YYYYMM Current FC Var])),ALL('Master Calendar Lookup'[Month]))

Thanks, E

[amitchandak]

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/

[2w]

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

[amitchandak]

@2w , Can you share sample data and sample output.

Appreciate your Kudos.

[2w]

Hi @amitchandak,

Please see file attached, sorry took me a while thanks for looking into if you have any suggestions how to solve.

https://www.dropbox.com/home?preview=Operational+Snapshot+Solutions+-+Copy.pbix

Let me know you have any suggestions how to solve this,

Best, E

[V-lianl-msft]

Hi @2w ,

The file does not exist. Please upload it again.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[2w · Accepted solution]

Hi y'all, thanks for your efforts but i sovled this the other day using variables.

E

[V-lianl-msft]

Hi @2w ,

Thanks for your update. As the problem is resolved. I will close this thread.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

It is asking for login

[2w]

Hey, @amitchandak 

Under the tab "SoUAR FY Development" I've started working on it, the formula "SoUAR FY Dev 2" was my attempt, you can see it's supposed to calculate and sum up Sum of USD Amount Rollup. The purposes with this formula is to create a Full Year Estimate so I've nested the calculate function with a second calculate function to include all months (the data is only monthly and not daily) but as you can see in the headers it doesn’t aggregate the values from the current forecast (See Current Forecast Version i built to identify which is the current forecast), I've built another measure where I don't use all months and it limits the data to the YYYY and MM selected in slicers. Any calculate function you can build which just evaluates and filters the current forecast from the Scenario Description column for the point in time selected by the users in slicers would be super helpful!! Sorry but I'm having issues attaching the PBI file to the message, would you mind shooting me an email at XXXXXX and I'll share with you a sample file.

Appreciate your efforts mate, E

[V-lianl-msft]

Hi @2w ,

Could you please share the sample pbix and the sample output through OneDrive for Business?(Please mask any sensitive data before uploading)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.