Same Period Last year comparison on the basis of quarter

[chayanupadhyay]

Hello,

I have scenario where i need to subtract latest year data (for all quarter) from previous year data (with the same quarter), PFB the sample data:

Period	Year	Quarter	Value	Year Rank
Q3 2019	2019	Q3	1	1
Q1 2018	2018	Q1	2	2
Q2 2019	2019	Q2	3	1
Q2 2018	2018	Q2	5	2
Q3 2018	2018	Q3	6	2
Q4 2018	2018	Q4	6	2

I need to create a measure to do following:

Calculate(SUM(Value),Filter(Table, Year Rank = 1)) - Calculate(SUM(Value), Filter(Table, Year Rank = 2))

I have 2019 and 2018 data and i need to perform below steps:

1.   Step A = Sum of 2019 data for all quarters  

2.   Step B = Sum of 2018 data for whatever quarter present in 2019 

3.    Step C = Step A - Step B

As per above data:

I have created rank for year

2019 = Year Rank 1

2018 = Year Rank 2

Step A:

I have two quarter data for year 2019 (Q2 2019 and Q3 2019)

 SUM(Value) Where Year Rank = 1

Step B: 

Now i need to sum of values for 2018 data but i need to restrict to only two quarters (Q2 2018 and Q3 2018)

I am having trouble writing the step B.

Appreciate your help in writing above measure logic.

Please let me know if you need more clarity on above scenario.

Thank you in advance 🙂

[v-lili6-msft · Accepted solution]

hi @chayanupadhyay 

You could try this formula:

Result = 

var _table=SUMMARIZE('Table','Table'[Period],'Table'[Year],'Table'[Quarter],"_value",CALCULATE(SUM('Table'[Value]))-CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])-1&&'Table'[Quarter]=MAX('Table'[Quarter]))))
return
SUMX(_table,[_value])

or

Result 2 = 
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[Period],
        'Table'[Year],
        'Table'[Quarter],
        "_value", IF (
            ISBLANK ( CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ),'Table'[Year] = MAX ( 'Table'[Year] ) - 1 && 'Table'[Quarter] = MAX ( 'Table'[Quarter] ) ) )),
            BLANK (),
            CALCULATE ( SUM ( 'Table'[Value] ) )
                - CALCULATE (SUM ( 'Table'[Value] ),FILTER (ALL ( 'Table' ),'Table'[Year] = MAX ( 'Table'[Year] ) - 1 && 'Table'[Quarter] = MAX ( 'Table'[Quarter] ) ) ) )
    )
RETURN
    SUMX ( _table, [_value] )

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[v-lili6-msft · Accepted solution]

hi @chayanupadhyay 

You could try this formula:

Result = 

var _table=SUMMARIZE('Table','Table'[Period],'Table'[Year],'Table'[Quarter],"_value",CALCULATE(SUM('Table'[Value]))-CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])-1&&'Table'[Quarter]=MAX('Table'[Quarter]))))
return
SUMX(_table,[_value])

or

Result 2 = 
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[Period],
        'Table'[Year],
        'Table'[Quarter],
        "_value", IF (
            ISBLANK ( CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ),'Table'[Year] = MAX ( 'Table'[Year] ) - 1 && 'Table'[Quarter] = MAX ( 'Table'[Quarter] ) ) )),
            BLANK (),
            CALCULATE ( SUM ( 'Table'[Value] ) )
                - CALCULATE (SUM ( 'Table'[Value] ),FILTER (ALL ( 'Table' ),'Table'[Year] = MAX ( 'Table'[Year] ) - 1 && 'Table'[Quarter] = MAX ( 'Table'[Quarter] ) ) ) )
    )
RETURN
    SUMX ( _table, [_value] )

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[chayanupadhyay · Accepted solution]

Thank you @v-lili6-msft  for your reply and solution.

I tried this approach and it worked for me and i found different solution as well.

I created a flag (custom column) to capture the quarter present for latest period and used that flag as filter while calculating the step 2 (calcualting the sum of values for previous year for same quarters present in latest year)

Thanks again for your help and time.

Regards,

Chayan

[parry2k]

@chayanupadhyay you should take advantage of time intelligence DAX functions. There are tons of functions available for compare period over period, here is link to time intelligence functions.

[chayanupadhyay]

Thank you @parry2k  for your reply.

I didn't have any unique date column for using the time intelligence function.

This is kind of extract of actual data which doesn't have all the information in terms of date.

Thanks for the reply, i am able to get through the problem by using the flag and putting the flag as filter.

Regards,

Chayan