Difference between two dynamic periords (Year or quarter based on filter)

[Anonymous]

Hello,

I've been trying to use dynamic time intelligence measures but I couldn’t get the right outputs…

I’d like to calculate the difference between two periods based on a filter (Yearly vs Quarterly). I have a filter that allows me to summary absolute values quarterly or yearly, but when I try to calculate the differences between the period and its previous, I can’t.

My data:

Datetime	Label	Amount
31/12/2018	1	100
31/12/2019	1	200
30/09/2020	1	300
31/12/2020	1	400
31/03/2021	1	500
31/12/2018	2	100
31/12/2019	2	200
30/09/2020	2	100
31/12/2020	2	100
31/03/2021	2	0

My output should be something like this:

- If Yearly filter is activated:

label	31/12/2018	31/12/2019	31/12/2020
1	null	+100%	+100%
2	null	+100%	-50%

- If Quarterly filter is activated, all available data, since december is Q4:

label	31/12/2018	31/12/2019	30/09/2020	31/12/2020	31/03/2021
1	null	null	null	+33%	+25%
2	null	null	null	0%	-100%

My measure is (# are notes, not in real code):

YOY% = 
VAR thisYear =
    SUM ( table[amount] )
VAR lastYear =
    CALCULATE ( SUM ( table[amount] ), DATEADD( table[datetime].[Date],-1, YEAR))
VAR lastQuarter =
    CALCULATE ( SUM ( table[amount] ), DATEADD(table[datetime].[Date],-1, QUARTER))
RETURN
     IF(COUNTROWS ( VALUES ( table[filter] ) ) = 1, ###if filter activated###
        DIVIDE ( thisYear - lastYear, lastYear, BLANK() ),
        DIVIDE ( thisYear - lastQuarter, lastQuarter, BLANK() )
    )

Two problems:

- I am getting good results when filter is not activated (second output table), but headers show only "year" and not quarters.

- It is calculating 2021 differences (-100%) with yearly filter when I do not have any Q42021 data.

Thank you very much.

[amitchandak]

@Anonymous , I think these are year and qtr behind measure. that you should switch based on selection

for this qtr vs last qtr diff

examples

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

year diff

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

//Only year vs Year, not a level below

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

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

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Of course it won't work. You have to get familiar with how time-intel in PBI works. Time-intel functions need a proper date table, which you don't have. To start you can read this: Time Intelligence in Power BI Desktop - SQLBI