Creating a Measure to Compare Aggregated Values Across Different Date Filters

[TomerIwanir1]

I'm working with two separate measures in Power BI, each calculating the sum of the same calculated column. However, each measure is filtered by a different date table to allow for time-based comparisons. I attempted to create a third measure that subtracts one from the other, but the result always returns zero. It seems that the context of the date filters is causing the measures to cancel each other out. I'm looking for a way to correctly compute the difference between these two measures while preserving their individual date contexts.

[v-kpoloju-msft]

Hi @TomerIwanir1,

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.

[v-kpoloju-msft]

Hi @TomerIwanir1,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @TomerIwanir1,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[burakkaragoz]

Hi @TomerIwanir1 ,

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.

[TomerIwanir1]

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 zero 

than I created another measure to subtract the other 2 but its not working, any ideas ?

 

[tharunkumarRTK]

@TomerIwanir1 

To handle such scenarios, you can create one date table and create one active and multiple inactive relationships between the date dimension and fact table. 

In your DAX measures, if you want to calculate based on active filter then you do not to modify the context, it is required only when you want to calcualte based on inactive relationship, for example:

Orders = COUNTROWS(Sales)


Orders Shipped =
CALCULATE(
    COUNTROWS(Sales)
    ,USERELATIONSHIP('Date'[Date], Sales[ShipDate])
)

If you want to create. a third measure, then you can do it easily 

Difference = [Orders] - [Orders Shipped]

follow this documet for more details

https://learn.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!