QTD Chg , YTD Chg and also difference between current quarter and previous quarter

Anonymous — Mon, 02 May 2022 13:45:48 GMT

Hi All-

Hoping to get help on this calculation. After creating a proper date table, on tryin to calculate the difference between Current Quarter and Previous Quarter, YTD Chg and QTD Chg, my result kept populating 0.00%.

Below are my codes for all 3 calculations.

This is the code to find the difference between the current quarter and previous quarter

QoQ diff =

VAR CurrentYearQuarter =

MAX ( 'Date'[Year Quarter Number] )

VAR CurrentSale =

SUM ('ICC Reports'[%] )

VAR LastSale =

CALCULATE (

SUM ('ICC Reports'[%] ),

REMOVEFILTERS ('Date'),

'Date'[Year Quarter Number] = CurrentYearQuarter

)

VAR PerviousSale =

CALCULATE (

SUM ('ICC Reports'[%] ),

REMOVEFILTERS ( 'Date' ),

'Date'[Year Quarter Number] = CurrentYearQuarter - 1

)

RETURN

IF (

HASONEVALUE ( 'Date'[Year Quarter Number] ),

CurrentSale,

PerviousSale - LastSale

)

This is the code for YTD Chg

YTD Chg =
IF(

ISFILTERED('Date'[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('Date'[Date])), QUARTER))

RETURN

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

)

Thank you.

Re: QTD Chg , YTD Chg and also difference between current quarter and previous quarter

amitchandak — Tue, 03 May 2022 02:20:09 GMT

Anonymous , Can you try time intelligence functions and try

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

YOY

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

or /

/Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence

topic QTD Chg , YTD Chg and also difference between current quarter and previous quarter in DAX Commands and Tips

QTD Chg , YTD Chg and also difference between current quarter and previous quarter

Re: QTD Chg , YTD Chg and also difference between current quarter and previous quarter