Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys, I am trying to create a dax measure that will return the value of hours for the succeeding week. I already have the correct dax for Week 1, however I am failing to get the correct value for week 2. Here's what I have so far:
Solved! Go to Solution.
Glad it worked sorry again for not reading well if you can please accept this post as solultion to help other people know its been solved
I'm not sure wich dates covers the returned values, but it is definitely inaccurate so far, may I know your suggestion to this?
hello if you haev salicer, then you need to add the remove filter on the week 2
instead of those you can create a week ID in your calendar table
Week ID =
VAR _min = YEAR(MIN(Calendar[Date]))
RETURN
//Choose WEEKNUM(Calendar[Date],2) for week start on monday or WEEKNUM(Calendar[Date],1) for week start on Sunday//
WEEKNUM(Calendar[Date],2) + (YEAR(Calendar[Date]) - _min) *53
now insert your filter slicer on the table ltes say you chose the start of week or week nb
use the measure
Week2DurationHours =
CALCULTAE(SUM('TimeEntry'[DurationHours]),Remove Filters(Date[your slicer]),Date[Week ID] = MAX(Date[Week ID]) - 1)
Hello, thanks for this. I think we are close - it only returns incorrect values. Here's the dax so far:
Ok did you make week id as a calculated column in your calendar , or measure? It needs to be a calculated column
And can you please show me more results as what you are getting ? Are there any other date filters? And i just reaf it again you want the next week not previous week? Then instead of -1
Week2DurationHours = CALCULATE(SUM('TimeEntry'[DurationHours]),REMOVEFILTERS('01_Date'[Start of Week]), '01_Date'[Week ID] = MAX('01_Date'[Week ID]) + 1)
Thanks again. I update the dax to +1 and it works! thank you so much
Glad it worked sorry again for not reading well if you can please accept this post as solultion to help other people know its been solved
Hello, sorry for the delay. I notice that our dax for week2duration returns all the sum of a row if there's no succeeding week ahead on a table. How can we fix it please? resharing the dax:
Hello, thanks for your reply. Here's my response:
Ok did you make week id as a calculated column in your calendar , or measure? It needs to be a calculated column - Yup created a calculated in 'Date' table and measure in TimeEntry table.
And can you please show me more results as what you are getting? - Its just random hours, not sure how to check as they filtered using a slicer. Are there any other date filters? None And i just reaf it again you want the next week not previous week? Then instead of -1 - Yup, next week please
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |