Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
79 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |