Switch between Current Period and YTD Calculation

Hello People!

Although it’s a bit late but I follow 2 rules..

= IF (
    Date[Month] = 'Jan' && -- Rule1
    'You haven't wished a new year yet' = TRUE (), -- Rule2
    'WISH NOW!!',
    'Wait for next year'
)

So.. Wish You a Very Happy New Year

And I do hope that you do a lot more interesting things this year than reading my poor jokes 😀

And to begin an interesting topic, I’d like to talk about how can you switch between a Current Period and YTD Calculation with a slicer. Essentially this is an offshoot of the blog that I wrote a while ago on how to change measures with a Slicer but I’d like to add an approach using Calculation Groups that makes this Calculation Dynamic.

Why don’t we start with Approach 1 – Using Plain Vanilla DAX

Start with a Video?

Here is the Setup!

A simple Pivot Table with Year and Month and Total Sales

My Need – I’d like to have a slicer to pick between Current Period Sales or YTD Sales and the Pivot Table should then display the results accordingly.

Adding a Slicer

I am going to create this Selection Table and make a slicer on Value Column.

As of now selecting the slicer does nothing, so let’s create a measure to reflect the calculation selected.

Creating a Measure to Switch between Current Period and YTD Sales

I write this simple Switch Function to toggle between Current Period Sales and YTD Sales.

Sales or YTD Displayed = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(SelectionTable[Value]) = 'Current Period', [Total Sales],
    SELECTEDVALUE(SelectionTable[Value]) = 'YTD', TOTALYTD([Total Sales],'Calendar'[Date]),
    [Total Sales]
)

In case you are curious, here is the link to understand the SELECTEDVALUE Function

If you place this measure in the pivot table, it works beautifully!

The only problem with this approach is that if I’d like to do the same with [Total Units], then I need a new measure with the same DAX which replaces [Total Sales] with [Total Units]. This approach is not dynamic to work on any measure placed in the Pivot Table.

This problem can be solved with the 2nd Approach – Calculation Groups

Calculation Groups to Switch Between Current Period and YTD Calculation

Calculation Groups are possible in Tabular Editor. If you have installed it, you’ll see it in the External Tools ribbon.

Open an instance of Tabular Editor and
Then let’s create a New Calculation Group

Note that I used the same DAX expression but just replaced [Total Sales] with SELECTEDMEASURE () to allow this calculation to run on any or all measures displayed in the visual. Sweet, let’s see the results.

I revise my Pivot Table with the following changes

Total Sales & Units measures added to my Pivot Table
In the column, I added Name column of the Calculation Group

Since both the Measures are visible, the YTD values are displayed for both Total Sales and Units.