Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am having a scenario where I need your help. I have been trying from the past few hours and somehow not able to think further. Please advise.
I will have to show week to date for the current week and previous week side by side as two KPI's. My date filter is as shown below. My latest week is marked as 'Latest Week' by using a DatePicker column.
so now, if user chooses 'Latest Week' from filter, then I need to display 'Current Week to Date SCR' and 'Previous Week to Date SCR'. For that I created two measures
************************************************************************
Current week to date SCR =
VAR CurrentDate = LASTDATE('Calendar'[CALNDR_DT])
VAR DayNumberOfWeek = WEEKDAY(LASTDATE('Calendar'[CALNDR_DT]),1)
return
CALCULATE(
[Service Change Requests],
DATESBETWEEN(
'Calendar'[CALNDR_DT],
DATEADD(CurrentDate,-1*DayNumberOfWeek,DAY),CurrentDate))
*************************************************************************************
Previous Week to Date SCR = CALCULATE([WTD SCR],DATEADD(Calendar[CALNDR_DT],-7,DAY),ALL('Calendar'))
**********************************************************************************************************
Now the requirement is, if the 'Latest Week' is chosen, then display above two measures
If the user chooses other than 'Latest Week' then display the below SCR measure whihc are simple and for whole weeks
@pprasad2 , refer how to handle such LOV
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
WOW /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...
example with Rank, Date table . Refer the links given
WTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Weekday] <=max('Date'[Weekday])))
LWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -1) && 'Date'[Weekday] <=max('Date'[Weekday])))
Thanks Amit. I was able to claculate the WTD, however I need a switch statement or an IF-ELSE to select the WTD's based on filter selection.
@pprasad2 Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample 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.
Thanks Greg for sharing this. I knew my question is a bit lengthy without data or workbook, I am sorry about that,
I almost figured out the WTD for current and prevuous weeks. I need your advise to write a switch statement or if-else to display one of them based on week filter. Here is it goes:
If the WEEK = Current Week, then display 'WTD-Current' else display 'WTD-Previous'
Note: WTD-Current and WTD-Previous are not scalar's. they are measures for revenue
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |