Relative date filter overriding ALL in measure

[Trif]

I have a measure that looks like this:

Income to date = CALCULATE(SUM(Payment[Amount]); FILTER(ALL(Payment[PaidDate].[Date]); Payment[PaidDate].[Date] <= MAX(Payment[PaidDate].[Date])))

As you can see, it sums up all payments up till the most recent payment in the current context by using ALL to ignore filters on the date. Keeping track of the different contexts in the formula is a bit confusing to me, but this seems to do what I want it to do.

However, I have run into one issue that I don't understand while creating visuals that depend on this measure. If I use simple filtering on Payment[PaidDate].[Month] and Payment[PaidDate].[Year], it behaves as expected, but if I use relative filtering on Payment[PaidDate] and set it to "within the month", I get incorrect output. From what I can see, the ALL seems to be ignored and the measure only sums up payments within the month selected by the filter.

In my head, it shouldn't matter which kind of filtering is used if their effective date range ends up being the same (e.g. 1/12/2019–31/12/2019 vs month = December & year = 2019). Can anyone explain why they behave differently?

[v-joesh-msft]

Hi @Trif ,

As shown in the figure below, when you use Payment [PaidDate]. [Date], the date hierarchy is referenced. When you use the relative slicer, the date is referenced.
Date and Date Hierarchy do have some differences, so different results will be presented. For more information, please refer to:

How to use Date vs Date Hierarchy in Power BI

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EQBgV57zUPpEuQg7HJ...

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Trif]

I think I understand, thanks. I can't quite get my measure to ignore filters for both the date and its hierarchy though; could you provide guidance on how to correct my expression?

[v-joesh-msft]

Hi @Trif ,

Maybe you can try to create a Calendar Table, Then use Calendar[Date] instead of Payment [PaidDate] after creating the relationship.

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.