Filter for Showing YTD & MTD at the same time

[Anonymous]

Hey All, 

Im making a dashboard for sales leader who were used to a slide deck and they want too see MTD and YTD in the same table. I did that no problem (pic 1), as you can see both YTD and MTD have different numbers (as they should). 

The Issue comes when I select any Month, when I do that both number's become the same (pic 2). How do I make a filter or filters that lets me select 1 month and it shows YTD and MTD on the same table without the numbers looking like picture 2?

Thanks,

Matt

[Anonymous]

HI @Anonymous,

It sounds like you want to prevent the filter effect that applies to your expression. (your slicer has limited the expression calculation range to a specific month, so the two formulas are calculated with the same range and get the same result)
For these types of effects, I'd not so recommend you to do these with time intelligence functions. (these functions filter range has been fixed internally and you cannot do custom with their filter ranges)

I'd like to suggest you add the 'all' function to ignore the filter effect and manually apply the filter effect to your expression.

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

In addition, you can also try to create an unconnected date table as the source of the slicer to use the slicer as a selector instead of the filter. You can use DAX expression to extract the selection value to calculate and the slicer should not be directly filtered on the fact table records.

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

If the year end is December and you select January as the month, then MTD and YTD will return the same figure.

[Greg_Deckler]

@Anonymous Would need to see your measure formulas to know for sure, but in general, you would use ALL or ALLEXCEPT to escape your current filter context and then you reimpose your desired filter context. So, you can use SUMX(FILTER(ALL('Table'),[Year] = MAX('Table'[Year]),[Value]) for example.

[Anonymous]

Hey @Greg_Deckler , 

I used the time intellegence formuals for MTD & YTD but for my difference measure I used Variables + Calculate. 

 

Would you use that formula when making the filter table?