Week by week difference using strings

[wawarefresher]

Hi everyone,

Some background on my report: I am trying to calculate the week-over-week difference. I created a date table and linked it to my report. I had to use the report-generated date for the main table. I extracted the report weekly because that's when we see the most change. The table is broken down into a few columns that aren't really useful to me such as classifications for what we're working on (class, description, height, etc) and the two valuable columns I have are strings (operating area and circuit numbers).

I am evaluating the different WO's completed weekly by each area. In the matrix, it seems to be multiplying every row and the total items left are way off so I turned that one off. I am trying to find a way to create a measure that shows me the total WO's left but I'm not sure how since as I stated above, the useful columns are strings. Whenever I try to create a chart, it keeps trying to total the count of every single WO instead of breaking it down into manageable numbers over time (week by week difference, total left in each area, etc). Is there any way to get around this?

[v-zhangti]

Hi, @wawarefresher 

Can you provide some sample data or simple pbix files? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

Best Regards,

Community Support Team _Charlotte

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

[amitchandak]

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

For week on week, you need week rank in week or date table

new columns
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

You can create new measures like

measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

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