Need help creating a measure for L4, L12 and L24 for a vertical bar chart, without calendar dates

[Anonymous]

Hello there,

My dates are not in calendar date form as they are in yearperiodweek form, relative to our fiscal calendar... example being 2021P1W02.

I have calculated measures for Ordered $ and shipped $, and I would like to have a vertical bar chart with a L4, L12 and L24 on the x axis with 2 bars for each value (ordered $ and shipped $ for L4, L12 and L24). The Y axis would the value/$. 

I do have the dates ordered by an order column corresponding to the dates, with the earliest date being 1 and the latest date being the largest number. Example. order 1 is 2019P1W01, 4 is 2019P1W04

Is there a way to create a formula or measure that lets me accomplish this? 

[amitchandak]

@Anonymous , this format seems sortable. See if my WOW approach with Rank can help you

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

rolling week example

Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

[Anonymous]

I'm trying to get this to work but my data is all in YearPeriodWeek Format, is there a way to acheive this without using calendar dates?

[amitchandak]

@Anonymous , I think you can. The only need is a separate table. Create a new table with the first 4,5 columns, Use summarize or distinct to get that table and join back with Year Pd Week

Now add these columns 

Year Period = [Year]*100 + [period]

Year Period Rank = RANKX(all('Date'),'Date'[Year Period ],,ASC,Dense) 

Year Week= [Year]*100 + [Week]

Year Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) 

Last 4 Week =CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Last 12 Week =CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-12 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

the same way you can create other measures, just use the required rank.

Hop this can help 

[Anonymous]

Alright I made a new date table with the below columns...

Sorry so when you say "Use summarize or distinct to get that table and join back with Year Pd Week" could you elaborate please? Do I just need to manage relationships in order to do that or how exactly would you go about doing this? Thanks for the help!

[Anonymous]

Sure, I appreciate the help! This has been frustrating me for 2 weeks now. I have uploaded to share point with my desired visuals, the hierarchy I wish to drill down, the table headers and actual table from BI (with random numbers). 

I appreciate any/all help as I try to get better with DAX and BI. Thanks so much! 

Link updated

https://1drv.ms/x/s!Aouvm3o2ZFPjgx4PV6mYxiv90RTt?e=KZrZYP