cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Why is weekly grouping returning wrong dates?

I am creating bins based of my Date field. I created two bins, one which retrieves the daily bins whilst the other bin is to return weekly dates. The daily bins returns the correct dates but the weekly bins is starting from a date which is not even present in my Date field.

How is this happening?

mdbuzzer_1-1603478102985.png

 

mdbuzzer_0-1603478046918.png

My date field starts from October 1st to October 16th, so why is it starting from September 27th?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Hi @mdbuzzer ,

 

After my researching, it is by design. Binning splits numeric or date/time data into equally sized groups. If we expand the date range a bit, you will find that October 1, October 2, and October 3 belong to the group beginning on September 27.

 

You could learn more from: 

 

https://dataveld.com/2018/05/10/manual-groups-bins-power-bi/

 

4.png

 

 

 

Best Regards,

Stephen Tao

 

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

3 REPLIES 3
Super User III
Super User III

That must be the result of the algorithm used to set up the bins.  You can create the week start dates starting on your min date as follows in a column, replacing "Dates" with your actual Date table name.

 

NewBin =
VAR thisdate = Dates[Date]
VAR mindate =
    MIN ( Dates[Date] ) // or use hard coded date like DATE(2020,10,1)
VAR numweeks =
    ROUNDDOWN (
        DATEDIFF (
            mindate,
            thisdate,
            DAY
        ) / 7,
        0
    )
RETURN
    mindate + 7 * numweeks

 

Regards,

Pat

 





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




I used that code to create a calculated column and the result was nearly right, but it starts with September 30th rather than October 1st. It should return October 1st, October 7th and October 14th.

 

mdbuzzer_0-1603566374363.png

 

Microsoft
Microsoft

Hi @mdbuzzer ,

 

After my researching, it is by design. Binning splits numeric or date/time data into equally sized groups. If we expand the date range a bit, you will find that October 1, October 2, and October 3 belong to the group beginning on September 27.

 

You could learn more from: 

 

https://dataveld.com/2018/05/10/manual-groups-bins-power-bi/

 

4.png

 

 

 

Best Regards,

Stephen Tao

 

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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors