DAX : Compare CURRENT YEAR and PREVIOUS YEAR in a group level

[rithu]

CODE RELEASE PERIOD count  SalesYearPeriod
A 202111 1 202207
A 202111 2 202201
A 202111 3 202107
A 202111 4 202102
A 202111 5 202008
A 202111 6 202002
B 202207 1 202208
B 202207 2 202201
B 202207 3 202108
B 202207 4 202102
B 202207 5 202003
C 202111 1 202209
C 202111 2 202204
C 202111 3 202109
C 202111 4 202106
C 202111 5 202011
D 202110 1 202208
D 202110 2 202108
D 202110 3 202103
D 202110 4 202008

This is the sample table, for every CODE (A,B,C,D,E,F) i have a release period, few SalesYearPeriod which is basicaly YYYYMM and $ SALES column. I added the count column using dax for every CODE using max salesyearperiod.

I need a DAX column that gives "full/half" FOR EVERY CODE based on the conditons,

IF RELEASE PERIOD IS INBETWEEN (SalesYearPeriod (COUNT =1) AND SalesYearPeriod(COUNT = 2) ) (previous year) THEN "HALF" 

ELSE IF  SalesYearPeriod (COUNT =1) > RELEASE PERIOD AND SalesYearPeriod (COUNT =1) > SalesYearPeriod(COUNT = 2)  THEN "FULL"

COUNT IS DYNAMIC AND SOME CODE JUST HAVE 1 OR 2 SalesYearPeriod

Thank you for you help. 

[Anonymous · Accepted solution]

Hi @rithu 

You can create the following code:

Column = var a=MAXX(FILTER('Table',[CODE]=EARLIER('Table'[CODE])&&[count]=EARLIER('Table'[count])+1),[SalesYearPeriod])
return SWITCH(TRUE(),[RELEASE PERIOD]<=[SalesYearPeriod]&&[RELEASE PERIOD]>=a,"HALF",[SalesYearPeriod]>[RELEASE PERIOD]&&[SalesYearPeriod]>a,"FULL")

Is this output you want?

Best Regards!

Yolo Zhu

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

[amitchandak]

@rithu , have a separate year table joined with year of your table 

then you can have measures like

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

not you can compare and create measure 

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

Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U