Distinctcount of the number of weeks within a rolling 8-week period

[Emink]

Hi all!

I am new to PowerBi, so I need som help...

I need to create a measure from table called "Bokningar", for distinctcount of the number of weeks where column Malltyp is "Vara Pris" within a rolling 8-week period. I need it for indicating how many weeks out of the last 8 weeks had bookings with Malltyp as "Vara Pris". I need to see the result for each week. 

The table has a date column for each row, so it should be based on that date. I tried several DAX measures to create the abovementioned calculation but I always get the wrong numbers.. Weekly(Vecka) results are either ones (1) or unrealistic numbers like 9 and 10. All the columns are properly formated with the right type of data type. 

I tried following DAX:

RollingCount_VaraPris_8Weeks =

CALCULATE(

    DISTINCTCOUNT(Bokningar[Vecka]),

    FILTER(

        ALL(Bokningar),

        Bokningar[Skapad] <= MAX(Bokningar[Skapad]) &&

        Bokningar[Skapad] > MAX(Bokningar[Skapad]) -56 &&

        COUNTROWS(

            FILTER(

                ('Bokningar'),

                'Bokningar'[Malltyp] = "Vara Pris"

            )

    )

    )

)  - Which returns 10s and 9s 

and

RollingCount_VaraPris_8Weeks 2 =

CALCULATE(

    DISTINCTCOUNT(Bokningar[Vecka]),

    FILTER(Bokningar,

    Bokningar[Skapad] <= MAX(Bokningar[Skapad]) &&

    Bokningar[Skapad] > Bokningar[Skapad] - 56 &&

    Bokningar[Malltyp]="Vara Pris"

    )) - Which return ones on weekly basis.

What am I doing wrong here? Any help and hints are appriciated.

Thanks!

[TheoC]

Hi @Emink 

I believe that it all depends on the current start and end weeks you have set in 'Date'[Week].  So if you have a start date and end date of each starting week, then there is potential to get up to 9 weeks (guaranteed).  The 10 weeks, however, I'm not sure.

Can you copy / paste your Date table with the Week number, start and end?

Thanks heaps,

Theo

[Emink]

Hi again @TheoC ,

I can share a pbx file with you and hopefullt it will bring more clarity. 

https://drive.google.com/file/d/1RjP-ZisoxC3-H-YQZEuVF6A-pW8XNBxJ/view?usp=drive_link

Maybe it is worth to mention that I live in Sweden, and somehow date formats might get messed up (not sure, just a heads up).

Thank you so much for trying to help!

Looking forward your response. 

/Emin

[TheoC]

Hi @Emink 

Can you please try the following:

RollingCount_VaraPris_8Weeks =

VAR CurrentDate = MAX ( Bokningar[Skapad] )

RETURN

CALCULATE (
    DISTINCTCOUNT ( Bokningar[Vecka] ) ,
    FILTER (
        ALL ( 'Bokningar' ) ,
        Bokningar[Skapad] <= CurrentDate &&  
        Bokningar[Skapad] > CurrentDate - 56 &&  
        Bokningar[Malltyp] = "Vara Pris" 
    )
)

The measure calculates the MAX date for context, then the distinct count of weeks, and the FILTER looks at ALL the rows in the 'Bokningar' table and filters accordingly.   The 56 is just 8 weeks x 7 days so you can amend as needed.

Hope this helps! 🙂

Theo

[Emink]

Hi @TheoC ,

thanks a lot for the response!

Please take a look at the screenshot below, i encounter the same problem when I am usiong my DAX...

I used your formula and here is how it looks like on the weekly basis:

It can't possibly be 10 discinct weeks wihtin a period of 8...

[TheoC]

Hi @Emink 

The issue is that the start and end of what constitutes a week is determined by the Date[Week] table and column.  Therefore, the first date that is used for to calculate the distinct count of weeks using Bokningar[Vecka] will be different to the first date of the week based on Date[Week].  Therefore, it is very likely you will get 9 weeks counted frequently.  

If you are using the TODAY() - 56, then there can theoretically be 57 days which has the potential to extend to 10 distinct weeks.  For example, if Date[Week] starts 1 January 2024 to 7 January 2024, and Bokningar[Vecka] starts on 7 January 2024 and ends 56 days (instead of 55 days) later, then the 8 weeks will finish on 4 March 2024.  As per below, the start period of 7 January 2024 is part of Date[Week] #1.  The end date of 4 March 2024 is part of Date[Week] #10.  Therefore, 10 distinct weeks is correct if you are using 56 + 1 days.  

Let me know if this makes sense.

Thanks heaps,

Theo

[Emink]

Hi Theo,

Thanks a lot for the explanation, it makes sense. So what would be the solution here? Reformating the start and end of the weeks? Besides the issue with 9s and 10s, the table in PowerBI displays wrong values for each week. For instance take Butik 244 for week 20 and 21 in 2024: the table displays "5" for each week which is completely wrong if you look into the Excel file that I attached here(the source of data):

Excel File 

According to the data from excel there should only be displayed 1 for week 21. I filtered the excel file so you could see it easily.

Thanks and looking forward to your explanation.

/Emin

[Emink]

Hi Theo,

Thanks a lot for the explanation, it makes sense. So what would be the solution here? Reformating the start and end of the weeks? Besides the issue with 9s and 10s, the table in PowerBI displays wrong values for each week. For instance take Butik 244 for week 20 and 21 in 2024: the table displays "5" for each week which is completely wrong if you look into the Excel file that I attached here(the source of data):

https://docs.google.com/spreadsheets/d/1_vn8lgkLm61YBcWmPtVBC7v-lN3SrTT-/edit?usp=drive_link&ouid=11...

According to the data from excel there should only be displayed 1 for week 21. I filtered the excel file so you could see it easily.

Thanks and looking forward to your explanation.

/Emin

[TheoC]

Hi @Emink 

I've tried to access the file and also the spreadsheet but have not had any luck.

Are you able to provide me with an XLSX file or PBIX that I can download or access to assist?

Thanks heaps.

Theo

[Emink]

Hi @TheoC 

please use the link I attached. It will open in google drive(sheets), but use the download option and select  (.xlsx) format. The file  should be available to anyone with the link. 

[TheoC]

Hi @Emink 

I also need the PBIX file. Unfortunately, I could not access it?

Thank you.

Theo

[Emink]

hi @TheoC ,

Please use the link below for both files:

Files