Calculate the week number from the last day of the week

[Kalnnie]

Hi everyone

I'm trying to create a calendar where the smallest unit is the week, but that still uses dates in the background.

all works fine until I add the month and year column, then I start to get double entries.

Does anyone know how to make Powerbi calculate the week number based on the last day of the week (Sunday of that week in my case) so that if a week overlaps two months, only one entry will apear corresponding to the latter month?

Here is the query:

Period Table =

VAR _MinDate =

    DATE ( 2022,08,01 )

VAR _MaxDate =

    TODAY ()

VAR _CalTable =

    CALENDAR ( _MinDate, _MaxDate )

VAR _YW =

    SELECTCOLUMNS (

        _CalTable,

        "WeekNum", WEEKNUM ( [Date] ,21),

        "Month",MONTH( [Date]),

        "Year", YEAR ( [Date] ),

        "YearWeek",

            YEAR ( [Date] ) * 100

                + WEEKNUM ( [Date] ,21)

    )

RETURN

    SUMMARIZE (

        ADDCOLUMNS ( _YW, "PeriodIndex", RANKX ( _YW, [YearWeek],, ASC, DENSE ) ),

        [YearWeek],

        [Month],

        [WeekNum],

        [Year]

    )

Thank you in advance for you support!

Kalnnie

[Kalnnie]

I actually got the desired outcome by creating a calendar table in powerquery, filtering only the sundays (the last day of the week in my case), then adding the required columns in DAX. What I got was a calendar with the weeknumbers as the lowest granularity level, but without the repeating values. 

[lbendlin]

You are looking for WEEKDAY function (DAX) - DAX | Microsoft Learn

My advice would be to compute this outside of Power BI, in a static reference table.