Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
Hi, @Creative_tree88
Aceess Denied.
It makes it easier to give you a solution.
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.
@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))
@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.
@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
@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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |