Forum Discussion
Current Week Value based on date field
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'.
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.
6 Replies
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 formatmeasures 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))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!
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?
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.
Hi, Creative_tree88
Aceess Denied.It makes it easier to give you a solution.
- Sample (dummy dataset) data as text, use the table tool in the editing bar
- Expected output from sample data
- 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 ZhengIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
