Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pprasad2
Helper I
Helper I

Current week WTD vs Previous Week WTD based on the 'Latest Week' value of the Data Filter

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.

 

pprasad2_0-1597108420754.png

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

 

SCR = SUMX('Customer Service Change Request', 'Customer Service Change Request'[SRVC_CHANGE_CNT])+0
PRIOR SCR = 
CALCULATE([Service Change Requests],DATEADD(Calendar[CALNDR_DT],-7,DAY),ALL('Calendar'))
 
I am struggling to put a SWITCH statement to display 'Week to Date' measure (Top2) when chosen 'Latest Week' and display (bottom2) measure when anything other than 'Latest Week' is chosen.
 
 
Please help. Could not share the work book for confidentaility reasons. Thank you

 

4 REPLIES 4
amitchandak
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.