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
Ron_FS
Frequent Visitor

Create "bin" starting from specific day of week

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!

 

1-31-2018 3-48-43 PM.jpg

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Ron_FS,

 

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
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
SreevidyaSwathi
New Member

Hi there, Anybody facing this issue check this

 https://community.powerbi.com/t5/Desktop/how-to-change-calendar-week-start-date-from-Sun-to-Mon/m-p/...

I was also facing the same issue and this post helped.


v-chuncz-msft
Community Support
Community Support

@Ron_FS,

 

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
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

@v-chuncz-msft 

 

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

Ron_FS
Frequent Visitor

Anybody?

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.