How to calculate YoY if date column is not there?

[A0S0CXG]

Hi,

I want to calculate YoY in following matrix where column headers are as shown below: I want to calculate YoY for the ones where this year(TY) and last year(LY) data is there in this matrix. I created dax for YoY as this where last week is all dynamic but it is giving 0 everywhere in divide function. Can you help get YoY rectified?

YoY % =

//VAR Week =

// SELECTEDVALUE(retailsummary[RetailHeader])

VAR Lastyear =

CALCULATE([Value], retailsummary[Scenario]="Actual", retailsummary[RetailFilter]="LY Last Week")

VAR Thisyear =

CALCULATE([Value], retailsummary[Scenario]="Actual" , retailsummary[RetailFilter]="Last Week")

VAR YoY =

IF( OR(CONTAINS(retailsummary,retailsummary[RetailFilter],"MTD"),CONTAINS(retailsummary,retailsummary[RetailFilter],"Last MTD")),

DIVIDE(CALCULATE([Value], retailsummary[Scenario]="Actual" , retailsummary[RetailFilter]="MTD"),

CALCULATE([Value], retailsummary[Scenario]="Actual" , retailsummary[RetailFilter]="Last MTD"), 0)-1,

DIVIDE(Thisyear , Lastyear, 0)-1

)

//VAR DisplayYoY=

// IF( SELECTEDVALUE(retailsummary[TYLY])="LY" || ABS(YoY)=1,

// BLANK(), YoY

// )

RETURN

YoY

 

[v-jayw-msft]

Hi @A0S0CXG ,

It's hard to check the DAX formula without any sample data. Please check the blog about how to How-to-Get-Your-Question-Answered-Quickly .

Best Regards,

Jay

[amitchandak]

@A0S0CXG ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Actually using rank or Year we can get this year vs last year

refer my blog if needed 

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 — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8