Forum Discussion
Creating a Measure to Compare Aggregated Values Across Different Date Filters
Hi Anonymous,
Thank you for reaching out to the Microsoft fabric community forum. Also thanks burakkaragoz, tharunkumarRTK, for those valuable insights for this thread.
After thoroughly reviewing the details you provided, I reproduced the scenario again, and it worked on my end. I used it as sample data and successfully implemented it.
outcome:
I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
Super UserHi Anonymous ,
To compare aggregated values across different date filters in Power BI, set up a single date table and connect it to your fact table with one active and one or more inactive relationships. In your DAX measures, use the USERELATIONSHIP() function to activate the appropriate relationship when needed.
For example, calculate one value using the active relationship, and for the other, use CALCULATE with USERELATIONSHIP to reference the inactive date relationship. Then, create your third measure by subtracting the two results. This method ensures each measure keeps its own date filter context, so your comparison works as intended.
If you want a step-by-step sample DAX or guidance for your exact model, just let me know.
translation and formatting supported by AI
So im not sure what im doing wrong, I have one date table (dates) which connected to a column named date in payroll table, than I created an Inactive vetween the date table th a column named "inactive dates" at the same payroll table,
than I created 2 measuers, one is sum of a column and created filters with the active relationship,
than a second measure
Sum Scenario 2 = CALCULATE(sum('Payroll table'[Apr POR]),USERELATIONSHIP(Dates[date],'Payroll table'[Date inactive])) is getting zerothan I created another measure to subtract the other 2 but its not working, any ideas ?