Creating Reusable Date Filters

Question

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

owenauger · Accepted Answer

Hi&nbsp;NicholasJackson&nbsp;
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.
&nbsp;
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.
&nbsp;
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.
&nbsp;
Here is an example of a calculation group (using DAX Script from Tabular Editor) with 5 Calculation Items:
&nbsp;
-----------------------------------------------------
-- 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
&nbsp;
Having created this Calculation Group, you can then apply a Calculation Item within a measure such as:
&nbsp;
Sales Quantity YTD = 
CALCULATE (
    [Sales Quantity],
    'Date Filter Calculation Group'[Date Filter] = "YTD"
)
&nbsp;
You can also filter on a particular Calculation Item in the report to apply it to multiple measures at a report/page/visual level.
&nbsp;
For general background on Calculation Groups, there are various articles such as:
https://www.sqlbi.com/calculation-groups/
&nbsp;
Regards

Forum Discussion

Creating Reusable Date Filters

2 Replies

Related content

How to create reusable function

Reusable slicer choices logic

reusable components in reports

Creating a reusable table filter

Reusable semantic model Best Approach