Get previous month / previous year using calculation groups

[ja1meee]

Hi all! 

Sorry for the long post but I am kinda stuck at the moment. Hear me out.

I am developing a report that has the following requirements:

1. 2 slicers (Slicer A & Slicer B), 

 
2. 2 date ranges for the custom options in Slicers A and B,  

3.  6 card visuals, and 3 filters 

Field used for Card Visual 1 : Column ABC
Field used for Card Visual 2:  Column DEF (this is basically column ABC but I created a measure to capture its data separately and use for Card Visual 2) 

The goal of the report is to have 2 separate values that will display on the Card Visuals. For example, if user selects Last 7 Days in Slicer 1 and Month over Month in Slicer 2, the Card 1 will show total values from the last 7 days while Card 2 will show the last 7 days total but 1 month prior, etc. Also, the date range slicers will only be usable if the CUSTOM option is selected for each slicer.

I tried these ff approaches:

Approach 1: Hard coding all (including the date ranges) inside the measures
1. Hard code the calculations inside the measures used for Card Visuals 1 & 2,
2. Create a dummy table for Slicers A and B to contain the fields that will serve as switch case triggers for the hard-coded values. 

Initially I thought this is good but I had an issue on the Custom option.However I could not make this calculation work when trying to get the Month over Month / Year over Year values. (I am using DAX function PARALLELPERIOD in Slicer B. )

Here is the calculation I used for capturing Custom in Slicer A. 

Note: I used DAX function USERELATIONSHIP in capturing the value for Custom in Slicer A. Part of the goal is that the date range will only function once Custom is selected in Slicer A. This is the part where I am stuck in this approach.

Approach 2: Use calculation groups
I tried to use calculation groups using the same logic I did in Approach 1.
Slicer A calculation group is working fine.
Slicer B calculation group is not working because I do not know how to implement a calculation group to interact on select measures. 


My question: Is Approach 2 better than Approach 1? If yes, can someone point me in the right direction? I am just using DAX function PARALLELPERIOD in getting the Month over Month and Year over Year values in Slicer B.

Note 2: Sorry could not provide pbix since report is inside CITRIX env. 

Many thanks, 
- ja1meee

[lbendlin]

What is the business problem you are trying to solve?

[ja1meee]

Hi @lbendlin , the client wants to compare the values generated from Slicer A from Slicer B. 

I was able to create a Calculation group with for Slicer A with the following measures:

Calculation Group for Slicer A

 

Calculation Group for Slicer A

// 7 Days

VAR __Isdatesfiltered =

    CALCULATE ( ISFILTERED ( 'dim_date'[date] ), ALLSELECTED () )

VAR __date =

    CALCULATE (MAX ( 'fact_table'[Date] ), REMOVEFILTERS () )

VAR __Result =

    IF ( __Isdatesfiltered,

        SELECTEDMEASURE (), CALCULATE ( SELECTEDMEASURE (),
            KEEPFILTERS (DATESINPERIOD ( 'dim_date'[date], __date - 1, -7, DAY ) ) ) )

RETURN

    __Result

// Last Month

VAR __Isdatesfiltered =

    CALCULATE ( ISFILTERED ( 'dim_date'[date] ), ALLSELECTED () )

VAR _end = CALCULATE ( MAX ( 'fact_table'[Prev Month End Date] ), REMOVEFILTERS () )

var _start = CALCULATE ( MAX ( 'fact_table'[Prev Month Start Date] ), REMOVEFILTERS () )

VAR __Days = 7

VAR __Result =

    IF ( __Isdatesfiltered, SELECTEDMEASURE (), CALCULATE ( SELECTEDMEASURE (),

            KEEPFILTERS ( FILTER ( ALL ( 'dim_date' ), 'dim_date'[date] >= _start && 'dim_date'[date] <= _end

            ) )))

RETURN

    __Result

// mtd

VAR __Isdatesfiltered = CALCULATE ( ISFILTERED ( 'dim_date'[date] ), ALLSELECTED () )

VAR _todaymonthstart = EOMONTH ( TODAY (), -1 ) + 1

VAR _todaymonthend =  CALCULATE ( MAX ( 'fact_table'[Date] ), REMOVEFILTERS ())
VAR __Result =

        CALCULATE (

             selectedmeasure(), KEEPFILTERS( FILTER ( ALL ('al_pres_dimad dim_date' ),

                'al_pres_dimad dim_date'[date] >= _todaymonthstart
      && 'al_pres_dimad dim_date'[date] <= _todaymonthend - 1  )))

RETURN

    __Result

// fy to date

VAR _start = MAXX ( ALLSELECTED ( 'fact_table' ), 'fact_table'[FY Start Date]  )

VAR _end =

    CALCULATE ( MAX ( 'fact_table'[Date] ), REMOVEFILTERS () )

VAR __Result =

    CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS (

            FILTER ( ALL ( 'dim_date' ), 'dim_date'[date] >= _start  && 'dim_date'[date] <= _end - 1

            )  )  )

RETURN

    __Result

and this is the measure I created for Slicer B

//for Primary vs Month on Month calculations
var _last7DaysMoM = CALCULATE([Actual],DATEADD('dim_date'[date],-1,MONTH))
var _mtdMoM = CALCULATE([Actual], DATEADD('dim_date'[date],-1,MONTH))
var _lastMonthMoM = CALCULATE([Actual], PARALLELPERIOD('dim_date'[date],-1,MONTH))
var _ytdMoM = CALCULATE( [Actual], DATEADD('dim_date'[date],-1,MONTH))

//for Primary vs Year on Year calculations
var _last7DaysYoY = CALCULATE([Actual], DATEADD('dim_date'[date],-1,YEAR))
var _mtdYoY = CALCULATE( [Actual ],DATEADD('dim_date'[date],-1,YEAR))
var _lastMonthYoY = CALCULATE( [Actual],PARALLELPERIOD('dim_date'[date],-1,YEAR))
var _ytdYoY = CALCULATE([Actual], DATEADD('dim_date'[date],-1,YEAR))

For the Slicer B, I was able to make it work for the Last 7 Days and FYTD and got the desired results. However, I am having issues with MTD and Last Month since it's returning blank values for the Slicer B. 

My follow up question is, should I adjust the calculation I have in Slicer A for the MTD and Last Month? or should I change the DATEADD dax function I used for the Slicer B? 

[lbendlin · Accepted solution]

I would go with Field Parameters and independent measures.

[ja1meee]

Thank you! This works for my report.