Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I looked on the forum but could not find the exact answer to this. If it exists, my apologies.
I have a transacation date for every day. I would like to create a 7 day bin that sums the associated values into 7 day groups. That is easy enough. However, the bin starts from sunday (I imagine this is a common problem). I would like the bin to start from another day of the week (such as friday or saturday). I saw some solutions that use the "WEEKDAY" function to subtract days of the week and create a calculated column. However, I need to include all 7 days of the week and the WEEKDAY function removes days. I really just want to shift the bin spectrum.
In the image below, the unbinned chart on the left ends on 1/26/2018. The 7 day binned chart on the right ends on 1/21/2018. I need this chart on the right to end on 1/26/2018 and bin by 7 days going back in time (binning 7 days for the past 6 months).
Any help is greatly appreciated!
Solved! Go to Solution.
You may use the following DAX to add a calculated column.
Column = VAR d = MAX ( Table1[Date] ) RETURN d - QUOTIENT ( DATEDIFF ( Table1[Date], d, DAY ), 7 ) * 7
Hi there, Anybody facing this issue check this
I was also facing the same issue and this post helped.
You may use the following DAX to add a calculated column.
Column = VAR d = MAX ( Table1[Date] ) RETURN d - QUOTIENT ( DATEDIFF ( Table1[Date], d, DAY ), 7 ) * 7
The downside to using this dax is that the start of the week isn't fixed so it results in a range that keeps moving (shows 7 days from whatever is the max date) as opposed to shifting the bin start date from Sunday to Monday or whatever day. That has been my struggle is I need to show week over week stats starting from Monday not Sunday
This code is awesome, but with my data set it calculates to a Tuesday, how would I go about changing it for it to be on a Friday
Anybody?