topic Re: Creating Reusable Date Filters in DAX Commands and Tips

Creating Reusable Date Filters

NicholasJackson — Tue, 08 Aug 2023 13:54:15 GMT

I have a simple measure that gets the total sales for the given year:

Total Sales (YTD) = CALCULATE(SUM([Sales Amount]), DATESYTD('Calendar'[Date]))

I have another measure where I would like to use the same date calculation, but instead use a different calculation:

Total Orders (YTD) = CALCULATE(COUNTROWS('Sales'), DATESYTD('Calendar'[Date]))

In this case, the filter calculation is quite simple as DATESYTD takes care of the heavy lifting. Copy/pasting the DATESYTD calcuation between measures isn't difficult or cumbersome. However, I have other date filters that are much more complex and need to be used in multiple measures.

How can I create a reusable (date) filter to use on multiple measures?

Few ideas:

I could create a calculated table, but this seems like far too much overhead if I have 10+ date filters.
This solution suggests creating a measure that simply has the filter criteria, but I'm afraid it may produce undesired results if used in the wrong context: https://community.fabric.microsoft.com/t5/Desktop/Creating-a-reusable-table-filter/m-p/1916927#M732781

Re: Creating Reusable Date Filters

OwenAuger — Wed, 09 Aug 2023 01:05:54 GMT

Hi NicholasJackson

I would recommend using Calculation Groups here.

Calculation Groups can be used to apply common modifications to multiple measures, with time intelligence being a common example.

The nice thing about Calculation Groups is that they can be applied either by a filter in the report, or by applying a filter in a DAX expression.

In your case, you could create a Calculation Group with a Calculation Item for each date filter. You can then apply a particular date filter to a calculation by filtering on the Calcualation Item by name, avoiding having to rewrite the DAX each time.

Here is an example of a calculation group (using DAX Script from Tabular Editor) with 5 Calculation Items:

----------------------------------------------------- -- Calculation Group: 'Date Filter Calculation Group' ----------------------------------------------------- CALCULATIONGROUP 'Date Filter Calculation Group'[Date Filter] CALCULATIONITEM "Default" = SELECTEDMEASURE () Ordinal = 0 CALCULATIONITEM "YTD" = CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Date'[Date] ) ) Ordinal = 1 CALCULATIONITEM "Full Year" = CALCULATE ( SELECTEDMEASURE (), PARALLELPERIOD ( 'Date'[Date], 0, YEAR ) ) Ordinal = 2 CALCULATIONITEM "Previous Full Year" = CALCULATE ( SELECTEDMEASURE (), PREVIOUSYEAR ( 'Date'[Date] ) ) Ordinal = 3 CALCULATIONITEM "YTD as at TODAY" = CALCULATE ( SELECTEDMEASURE (), CALCULATETABLE ( DATESYTD ( 'Date'[Date] ), 'Date'[Date] = TODAY () ) ) Ordinal = 4

Having created this Calculation Group, you can then apply a Calculation Item within a measure such as:

Sales Quantity YTD = CALCULATE ( [Sales Quantity], 'Date Filter Calculation Group'[Date Filter] = "YTD" )

You can also filter on a particular Calculation Item in the report to apply it to multiple measures at a report/page/visual level.

For general background on Calculation Groups, there are various articles such as:

https://www.sqlbi.com/calculation-groups/

Regards

Re: Creating Reusable Date Filters

NicholasJackson — Tue, 15 Aug 2023 17:34:32 GMT

This is exactly what I was looking for, thank you so much!