Notifications
Clear all
Power Pivot
3
Posts
2
Users
0
Reactions
78
Views
Topic starter
Hi,
When creating variance measures, variances are not shown when no data is found in the first part of the formula
=[Sum of FTE]-CALCULATE([Sum of FTE];PREVIOUSYEAR(Tbl_FTEsampledata[Date]))
I have added some sample data in attachment to illustrate the problem. I already tried some things, like adding a nested ISBLANK formula, but I do not seem to get it right.
- I am looking for a solution to avoid having to add zero records in the data table to solve the issue.
- The other way around, when there is no result for the second part of the formula, it is treated as zero and the calculation is done correctly
Kr Claudine
Posted : 29/11/2019 12:44 pm
Hi Claudine,
When using Time Intelligence functions, like PREVIOUSYEAR, you must use the date fields from your calendar table, not the fields from your fact table. Remove the Date field from the PivotTable and replace it with the date field from your calendar table. Likewise in the measure.
=SUM([FTE])-CALCULATE([Sum of FTE],PREVIOUSYEAR('Calendar'[Date]))
Mynda
Posted : 29/11/2019 8:54 pm
Topic starter
Great, thank you Mynda, once more 🙂
Posted : 30/11/2019 3:27 am
