How can I compare a CONSTANT year (i.e 2019) with other years (i.e 2020, 2021, 2022...) ?

[JFS]

Hi, everybody,

I am a newer in DAX so maybe my question has a simple answer, but I have been struggling with it for so long and I do not find it.

I want to calculate the %YoY variation of a given meassure but not between two correlative years but every year (2020, 2021, 2022 when it comes) over one specific year (2019): %2020o2019, %2021o2019, %2022o2019 ...

If we look at the DAX code of Quick Meassure of %YoY, we can see that the Var _PREV_YEAR is defined like that:

    VAR __PREV_YEAR =

        CALCULATE(

            [meassure],

            DATEADD(<Date>[Date].[Date], -1, YEAR)

being  [meassure] a meassure I have defined previously that I want to compare over the years, and <Date> a date table I have previuosly created.

DATEADD returns a table containing a column of dates that have been shifted forward or backward in time by the specified number of intervals from the current context dates. 

BUT, since what I want is something that returns a table containing a column of dates OF A CONSTANT YEAR (say, 2019) and not a column of shifted dates, I came up with this rewriting for that chunk of code with DATESBETWEEN:

    VAR __PREV_YEAR=

        CALCULATE(

            [meassure],

             DATESBETWEEN(<Date>[Date].[Date], DATE(2019,1,1),DATE(2019,12,31))

        )

    RETURN

The problem is that, with that code, what I have in return is a table containing a column of dates, for every year, with the TOTAL value from 2019 in every month (DAX does not evaluate the context of every month, I guess). I would like to write a code that can return for every year, month to month, the variation against 2019; for instances, in the year 2020, the variation january2020vs january2019, february2020 vs february2019, march2020 vs march2019etc, and in 2021, the variation january2021vs january2019, february2021 vs february2019, march2021 vs march2019; and so on with every year in the future.

For some reason that I do not undestand, with DATESBETWEEN, DAX does not evaluate the context of the months filtered (whereas it does it with DATEADD)

As I told you at the begining, I have been striggling with it for long but I do not find the solution, so any help would be very much appreciated.

Thanks in advance.

[v-xiaoyan-msft]

Hi @JFS ,

Glad that your problem has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

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

[JFS]

Done!

[JFS]

Than you very much, @amitchandak !

It works with this chunk, from what you have posted (in blue )

    VAR __PREV_YEAR=

        CALCULATE(

            [meassure],

            FILTER(ALL(<date>[Date].[Year]),<date>[Date].[Year]=2019)

        )

    RETURN

I kind of undertand how it works out, but I have to think more about it in order to understand it completely.  I imagine that with ALL(), I am ingoring the previous filters and, next step, I am setting a new filter (Year = 2019) for DAX to evaluate the meassure only in 2019. Something like that, I guess.

Thanks a million!

[amitchandak]

@JFS , Yes , all is doing that.

Also, try to avoid using date hierarchy from date Table, once mark it as date table you will not get that

refer this for filter in calculate

http://dataap.org/blog/2019/04/22/difference-between-calculate-with-and-without-filter-expression/

[JFS]

Thanks again for the comment, @amitchandak and for the link. They are very useful.

[amitchandak · Accepted solution]

@JFS , You can get year 2019 like

M2019=

CALCULATE(
[meassure],
year('Date'[Date])=2019)

or

M2019=

CALCULATE(
[meassure],filter(all(Date[Year]), 'Date'[year]=2019))

this case if year is in visual, diff =  [measure] -[M2019]

for other you can use time intelligence 

examples

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

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

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