topic Re: Change calculation between Current year Qtr1 and previous year Qtr4 in DAX Commands and Tips

Change calculation between Current year Qtr1 and previous year Qtr4

Anonymous — Fri, 29 Apr 2022 16:18:15 GMT

Hi All-

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

Re: Change calculation between Current year Qtr1 and previous year Qtr4

tamerj1 — Sun, 01 May 2022 10:29:00 GMT

Hi Anonymous

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 )

Re: Change calculation between Current year Qtr1 and previous year Qtr4

Anonymous — Sun, 01 May 2022 13:44:19 GMT

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

Re: Change calculation between Current year Qtr1 and previous year Qtr4

tamerj1 — Sun, 01 May 2022 14:37:22 GMT

Anonymous

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

Re: Change calculation between Current year Qtr1 and previous year Qtr4

Anonymous — Sun, 01 May 2022 15:30:24 GMT

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]))

Re: Change calculation between Current year Qtr1 and previous year Qtr4

tamerj1 — Sun, 01 May 2022 17:34:53 GMT

Anonymous

great

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

Re: Change calculation between Current year Qtr1 and previous year Qtr4

Anonymous — Sun, 01 May 2022 20:51:41 GMT

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)

)

Re: Change calculation between Current year Qtr1 and previous year Qtr4

Anonymous — Sun, 01 May 2022 21:28:42 GMT

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

)

Re: Change calculation between Current year Qtr1 and previous year Qtr4

tamerj1 — Sun, 01 May 2022 21:47:34 GMT

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

Re: Change calculation between Current year Qtr1 and previous year Qtr4

Icey — Mon, 09 May 2022 07:26:34 GMT

Hi Anonymous ,

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.