Why is weekly grouping returning wrong dates?

[mdbuzzer]

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?

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

[Anonymous · Accepted solution]

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/

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.

[mahoneypat]

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

[mdbuzzer]

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.