Change calculation between Current year Qtr1 and previous year Qtr4

[RilwanFlame]

Hi All- 

Im looking to calculate change between Current year Qtr1 and previous year Qtr4. Thank you

[Icey · Accepted solution]

Hi @RilwanFlame ,

Try to use DATEADD function like so:

Difference =
VAR PreQuar_ =
    CALCULATE ( SUM ( 'Table'[Value] ), DATEADD ( 'Table'[Date], -1, QUARTER ) )
VAR ThisQuar_ =
    SUM ( 'Table'[Value] )
RETURN
    ThisQuar_ - PreQuar_

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @RilwanFlame 

You can utilize the Row Totals to display the change between the last two quarters. Assuming the original measure is SUM ( Table[Sales] ) and that you have a year-quarter column in your date table in the format YYYYQ

then [Sales Amount] can be modified to something like

Sales Amount =
VAR CurrentYearQuarter =
    MAX ( 'Date'[Year Quarter] )
VAR CurrentSales =
    SUM ( Table[Sales] )
VAR LastSales =
    CALCULATE (
        SUM ( Table[Sales] ),
        REMOVEFILTERS ( 'Date' ),
        'Date'[Year Quarter] = CurrentYearQuarter
    )
VAR PerviousSales =
    CALCULATE (
        SUM ( Table[Sales] ),
        REMOVEFILTERS ( 'Date' ),
        'Date'[Year Quarter] = CurrentYearQuarter - 1
    )
RETURN
    IF (
        HASONEVALUE ( 'Date'[Year Quarter] ),
        CurrentSales,
        PerviousSales - LastSales
    )

[RilwanFlame]

Hi Tamerj-

So, i tried to follow your script  which i have below but getting and error:

Quarterly Chg Diff =

VAR CurrentYearQuarter =

MAX ( 'Calendar'[YearQuarter] )

VAR CurrentSale =

SUM ('ICC Reports'[%] )

VAR LastSale =

CALCULATE (

SUM ('ICC Reports'[%] ),

REMOVEFILTERS ('Calendar'),

'Calendar'[YearQuarter] = CurrentYearQuarter

)

VAR PerviousSale =

CALCULATE (

SUM ('ICC Reports'[%] ),

REMOVEFILTERS ( 'Calendar' ),

'Calendar'[YearQuarter] = CurrentYearQuarter - 1

)

RETURN

IF (

HASONEVALUE ( 'Calendar'[YearQuarter] ),

CurrentSale,

PerviousSale - LastSale

)

[tamerj1]

YearQuarter column should be of integer data type. I sent you a code for a datetable in a private message. Please apply the code to create a new date table. The only thing you need to do is to replace Sales[Order Date] with name of the date column in your sales table. Then you can use the [Year Quarter Number] column in the measure's code

[RilwanFlame]

Hi Tamerj- 

Thank you for your response. I do not have a date table (calendar table) created. I am using the Date field coming directly from my dataset and it does not have year-quarter. 

What if i want to create a calendar(date) table with a year-quarter in it and then with a relationship to my actual dataset table. 

Thank you

Thank you 

[tamerj1]

@RilwanFlame 

Yes you haveto have  a DateTable with at least date, year, quarter and year-quarter. Then build one to many relationship with the sales fact table. In your table visual use the date attributes from the DateTable. Then it should work. 
creating such table shall not be an issue. If your having trouble creating it please let me know

[RilwanFlame]

Yes  please, i will need help creating that. 

So far, i have the below created

Calendar = addcolumns( Calendarauto(),

"Year", Year([Date]),

"MonthNo", Month([Date]),

"Month", Format([Date],"mmm"),

"QuarterNo", Quarter([Date]),

"YearQuarter", "Q" & Quarter([Date]) & " " & Year([Date]))

[tamerj1]

@RilwanFlame 

great 

"YearQuarter", Quarter([Date]) + 10 * Year([Date])

[RilwanFlame]

Hi Tamerj- 

Im stil working on getting the difference between the current quarter and previous quarter. 

After creating the Date table, some DAx measure created got messed up. Like the Quarter To Dtae Change (QTD Chg) and Year To Date Change (YTD Chg). it start populating 0.00% 

 Before creating the Date table, this use to be my formula for QTD Chg:

QTD Chg=

IF(

    ISFILTERED('ICC Reports'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_QUARTER = CALCULATE(SUM('ICC Reports'[Decimal]), DATEADD('ICC Reports'[Date].[Date], -1, QUARTER))

    RETURN

        DIVIDE(SUM('ICC Reports'[Decimal]) - __PREV_QUARTER, __PREV_QUARTER)

)

And for YTD Chg:

IF(

ISFILTERED('ICC Reports'[Date]),

ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

VAR __PREV_YE = CALCULATE(SUM('ICC Reports'[Decimal]), DATEADD('ICC Reports'[Date].[Date], -Quarter(SELECTEDVALUE('ICC Reports'[Date])), QUARTER))

RETURN

DIVIDE(SUM('ICC Reports'[Decimal]) - __PREV_YE, __PREV_YE)

)