Dynamically calculate YoY sales based on string date slicer

[dokat]

Hi,

I have period table that is a string  in text format. My question is if there is a way to dynamically calculate year over year  based on period slicer selection?

Period

4 Weeks (Jan 03 2021 -  Jan 30 2021)

4 Weeks (Jan 02 2022 -  Jan 29 2022)

13 Weeks (Nov 01 2020 -  Jan 30 2021)

13 Weeks (Oct 31 2021 -  Jan 29 2022)

52 Weeks (Feb 02 2020 -  Jan 30 2021)

52 Weeks (Jan 31 2021 -  Jan 29 2022)

	TY	LY	YoY % Chg
Yogurt
Dairy
Cream
Cheese

[amitchandak]

@dokat , Are these 4 week periods, 13 periods for 1 year?

Usually, we try to create a rank and use that to do period-over period analysis. we need YYYYPP format for that

refer : https://youtu.be/aU2aKbnHuWs?t=4759

[dokat]

@amitchandak Yes they are 4 week periods, 13 periods for 1 years But data is in text sring. not sure how i can convert it to YYYYPP period. Can it be managed through if functions?

[Anonymous]

Hi @dokat ,

In order to provide you a suitable solution, could you please share  more data (including the fact table data) and the final result you want with us? And use some specific examples to illustrate how to dynamically display YoY values when choosing different Period with Text type?

Best Regards

[dokat]

@Anonymous Thanks for you response. This issue is resolved.

[Anonymous]

Hi @dokat ,

Thanks for your feedback. It's glad to hear that the issue has been resolved. Would you be willing to share your solution here and mark your post as Answered? This will also help others in the community with similar problem to yours to find a solution easily. Thank you for your sharing and  time. Much appreciated!

Best Regards

[dokat]

@Anonymous the problem i have right now is to add "YTD LY", "Last Month LY", "Last YearLY" to the calculated column

Ideal State

1/31/2022 = YTD

1/31/2021 = YTDLY

1/1/2022 = Last Month

1/1/2021 = Last MonthLY

12/31/2021 = Last Year

12/31/2020 = Last YearLY

[dokat · Accepted solution]

@Anonymous I used below code to create calculated column and bring in new calendar table and link it.

Slicer = var max_date = calculate(max(CY[Calendar Year]),all()) return switch(true(), 
CY[Calendar Year]= max_date,"YTD", 
and(MONTH(CY[Calendar Year]) = month(max_date),YEAR(CY[Calendar Year]) = YEAR(max_date)),"Last Month", 
CY[Calendar Year]= DATE ( YEAR (max_date)-1, 12, 31 ),"Last Year")