Current Week Value based on date field

[Creative_tree88]

Hi - I am hoping you can help!  I suspect this is pretty simple but it's really late, I am a little stressed out as I need to get this sorted by tomorrow...really hope you guys can come to the rescue again.

I have a dataset (simplified version attached).  I need to be able to show which modality has changed the most from current week to previous week (dataset is updated every week so will need to dynamically update each week).

I am only interested in 'Req to Att' field (in column B - Report Name).  And I only want to show (on 2 different cards) the change from current to previous week for 'Req to Att' which are less than 7 days and 'Req to Att' which are over 14 days).

So, in other words, one card will show the modality (CT, MRI etc) which has increased the most (in real terms and % terms also) from current to previous week for Req to Att over 7 days.  And on another card, I need to show the modality which has increased the most (in real terms and % terms) from current to previous week for Req to Att over 14 days.

I guess a rank system would be useful, to show the 3 highest changes (real and %), for 7 days and 14 days if possible?  I'm trying to make it easy for my users to identify the areas of concern without having to delve into the data themselves!

Even though I've supplied data spanning from 2020, it's only the most current and previous week to this, I'm interested in showing on the cards as this will change every week this report is updated.

Would really appreciate any help on this!

The week is based on 'Week Commencing' column but I have also set up a separate calendar table which allocates a week number to each of these dates if that helps??  The calendar table is called 'Cancer Report Week Lookup'.

Current to Previous Week Change Sample Test 

[v-angzheng-msft]

Hi, @Creative_tree88 

Aceess Denied.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented? 

Best Regards,
Community Support Team _ Zeon Zheng

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

[amitchandak]

@Creative_tree88 , expected output is not very clear. need example.

For WOW

new columns  in date table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures needed ]
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))

[Creative_tree88]

@amitchandak and @v-angzheng-msft 

Apologies - I thought I'd made this test data available.  Please find link below, with expected result and a few words regarding what I'd like it to 'say' ideally?

Week on Week with expected Result 

[v-angzheng-msft · Accepted solution]

Hi, @Creative_tree88 

Try to create measures as following:

Result:

Please refer to the attachment below for details.

Note: Only results older than 14 days are included in the attachment, you can switch measures to get results younger than 7 days.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Creative_tree88]

@v-angzheng-msft  - many thanks for your help with this.  I'll study the attachment and your suggested technique and hopefully it'll work exactly as planned!  Best regards

[Creative_tree88]

@amitchandak - many thanks for your reply.  I've added an extra tab to the sample data I sent.  Link below.  I basically want to extract the key information (perhaps even rank the modalities i.e. CT performed best, followed by MRI, then US - for example).  The example I've given is for patients waiting over 14 days but I also need to show those who were seen within 7 days.  Obviously I want to see more in this timeframe than waiting over 14 days!

I'd like to show this as a card, with text, or happy just to show in a ranked table with the respective changes detailed in this table.

Many thanks in advance!

Sample Week on week