Creating Reusable Date Filters

[NicholasJackson]

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#M7327...

[OwenAuger · Accepted solution]

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

[NicholasJackson]

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