Display Current Week Number 99 starting from 03 December 2017 week starting on Monday to Sunday.

[Anonymous]

Hello Community,

This project started on 03 December 2017 on Sunday. Week starts from Monday through to Sunday. FYI - Project will go until 2022 and FYE is 30th June.

I want the week number to change dynamically every week to reflect the current week since 03 December 2017 as shown in the screenshot. Also, just below the report title, I want to show reporting period dates for the last week as shown in the screenshot.

I have created a DAX table by copying code from https://www.sqlbi.com/articles/using-generate-and-row-instead-of-addcolumns-in-dax/ but am not sure how to get the Week Number 99? The screenshot shows the data I've got thus far in the Date table.

After achieving this, I need to get the reporting period incorporated dynamically as well.

All the 12 tables have the same date and the data looks similar to the screenshot.

I thought I would use DAX instead of M Language Script because I could re-use this DAX Date table for other reports waiting to be developed on this project.

Can anyone please let me know how to achieve this? Any guidance is greatly appreciated!

Thanks in advance!

Kind regards,

Bhoga

 

[Anonymous · Accepted solution]

CALENDAR 5 = 
VAR BASECALENDAR =
    CALENDAR ( DATE ( 2017, 12, 03 ), DATE ( 2022, 06, 30 ) )
RETURN
    GENERATE (
        BASECALENDAR,
        VAR BASEDATE = [DATE]
        VAR YEARDATE = YEAR ( BASEDATE )
        VAR MONTHNUMBER = MONTH ( BASEDATE )
        VAR MONTHNAME = FORMAT ( BASEDATE, "MMMM" )
        VAR YEARMONTHNAME = FORMAT ( BASEDATE, "MMM YY" )
        VAR YEARMONTHNUMBER = YEARDATE * 12 + MONTHNUMBER -1
        VAR WeekNumber = FORMAT(WEEKNUM(BASEDATE,2),"00")
        RETURN ROW (
            "DAY", BASEDATE,
            "YEAR", YEARDATE,
            "MONTH NUMBER", MONTHNUMBER,
            "MONTH", MONTHNAME,
            "YEAR MONTH NUMBER", YEARMONTHNUMBER,
            "YEAR MONTH", YEARMONTHNAME,
            "Year Week Number",CONCATENATE(YEARDATE,WeekNumber)
        )
    )

Calculated Column = RANKX(FILTER(ALL('CALENDAR 5'[Year Week Number]),RIGHT('CALENDAR 5'[Year Week Number],2)<>"53"),'CALENDAR 5'[Year Week Number],,ASC)-1

@Anonymous Hi 
Can you please add Year week number in your calendar table (1 st code) and then create a column to get the week number
In case you want to exclude week number 53 for each year use

YearWeekNumber =

RANKX(FILTER(ALL('CALENDAR 5'[Week Number]),RIGHT('CALENDAR 5'[Week Number],2)<>"53"),'CALENDAR 5'[Week Number],,ASC)-1

[Anonymous · Accepted solution]

Expected = RANKX(FILTER(ALL('CALENDAR 5'[Year Week Number]),RIGHT('CALENDAR 5'[Year Week Number],2)<>"53"),'CALENDAR 5'[Year Week Number],,ASC)-1

CALENDAR 5 = 
VAR BASECALENDAR =
    CALENDAR ( DATE ( 2017, 12, 03 ), DATE ( 2022, 06, 30 ) )
RETURN
    GENERATE (
        BASECALENDAR,
        VAR BASEDATE = [DATE]
        VAR YEARDATE = YEAR ( BASEDATE )
        VAR MONTHNUMBER = MONTH ( BASEDATE )
        VAR MONTHNAME = FORMAT ( BASEDATE, "MMMM" )
        VAR YEARMONTHNAME = FORMAT ( BASEDATE, "MMM YY" )
        VAR YEARMONTHNUMBER = YEARDATE * 12 + MONTHNUMBER -1
        VAR WeekNumber = FORMAT(WEEKNUM(BASEDATE,2),"00")
        RETURN ROW (
            "DAY", BASEDATE,
            "YEAR", YEARDATE,
            "MONTH NUMBER", MONTHNUMBER,
            "MONTH", MONTHNAME,
            "YEAR MONTH NUMBER", YEARMONTHNUMBER,
            "YEAR MONTH", YEARMONTHNAME,
            "Year Week Number",CONCATENATE(YEARDATE,WeekNumber)
        )
    )

[Anonymous · Accepted solution]

Hi @Anonymous 
If 04 November 2019 to 10 November 2019 is Week number 100 as per your business requirement than above code will do the work for you

[Anonymous · Accepted solution]

Hi @Anonymous Please create following measure to get previous week and previous week date range

PreviousWeek = 
VAR weekNumber = CALCULATE(MAX('CALENDAR 5'[Column]),FILTER('CALENDAR 5','CALENDAR 5'[Date]=TODAY()))
RETURN CONCATENATE("Week",weekNumber-1)

PrevWeekRange = 
VAR _week = CALCULATE(MAX('CALENDAR 5'[Column]),FILTER('CALENDAR 5','CALENDAR 5'[Date]=TODAY()))-1
VAR _start = CALCULATE(MIN('CALENDAR 5'[Date]),FILTER('CALENDAR 5','CALENDAR 5'[Column]=_week))
VAR _end = CALCULATE(MAX('CALENDAR 5'[Date]),FILTER('CALENDAR 5','CALENDAR 5'[Column]=_week))
RETURN CONCATENATE("Reporting Period",CONCATENATE(_start,CONCATENATE("-",_end)))

[Anonymous]

Anyone..!

Thanks,

Bhoga

[Anonymous · Accepted solution]

Expected = RANKX(FILTER(ALL('CALENDAR 5'[Year Week Number]),RIGHT('CALENDAR 5'[Year Week Number],2)<>"53"),'CALENDAR 5'[Year Week Number],,ASC)-1

CALENDAR 5 = 
VAR BASECALENDAR =
    CALENDAR ( DATE ( 2017, 12, 03 ), DATE ( 2022, 06, 30 ) )
RETURN
    GENERATE (
        BASECALENDAR,
        VAR BASEDATE = [DATE]
        VAR YEARDATE = YEAR ( BASEDATE )
        VAR MONTHNUMBER = MONTH ( BASEDATE )
        VAR MONTHNAME = FORMAT ( BASEDATE, "MMMM" )
        VAR YEARMONTHNAME = FORMAT ( BASEDATE, "MMM YY" )
        VAR YEARMONTHNUMBER = YEARDATE * 12 + MONTHNUMBER -1
        VAR WeekNumber = FORMAT(WEEKNUM(BASEDATE,2),"00")
        RETURN ROW (
            "DAY", BASEDATE,
            "YEAR", YEARDATE,
            "MONTH NUMBER", MONTHNUMBER,
            "MONTH", MONTHNAME,
            "YEAR MONTH NUMBER", YEARMONTHNUMBER,
            "YEAR MONTH", YEARMONTHNAME,
            "Year Week Number",CONCATENATE(YEARDATE,WeekNumber)
        )
    )

[Anonymous]

Hi@Anonymous

Your code works.. amazing! thank you so much.

Yes, you're right. the week actually starts from 27 November 2017.

Is there any change I need to do into your coding for this to work correctly?

Note: To make it very clear to you.. Current Week from 04 November 2019 to 10 November 2019 is Week 100 as per my Business confirmation.

Thanks a million.

Kind regards, B

[Anonymous · Accepted solution]

Hi @Anonymous 
If 04 November 2019 to 10 November 2019 is Week number 100 as per your business requirement than above code will do the work for you

[Anonymous]

Hi @Anonymous

Thanks a million for the timely help, greatly appreciated.

Kind regards, B

[Anonymous]

Hello Experts,

I actually needed to display Last Week Number which is 102 and Last Week Period which is 18/11/2019 to 24/11/2019 at the top of my Power BI Report.

I managed to get the Week Number right 102 but unable to work out my way to the Reporting Week Period!

Attached is the screenshot FYR.

Can you please let me know what changes should I be doing to the 'Week Start & End Date' Measure?

Thank heaps.

Kind regards, B

[Anonymous · Accepted solution]

Hi @Anonymous Please create following measure to get previous week and previous week date range

PreviousWeek = 
VAR weekNumber = CALCULATE(MAX('CALENDAR 5'[Column]),FILTER('CALENDAR 5','CALENDAR 5'[Date]=TODAY()))
RETURN CONCATENATE("Week",weekNumber-1)

PrevWeekRange = 
VAR _week = CALCULATE(MAX('CALENDAR 5'[Column]),FILTER('CALENDAR 5','CALENDAR 5'[Date]=TODAY()))-1
VAR _start = CALCULATE(MIN('CALENDAR 5'[Date]),FILTER('CALENDAR 5','CALENDAR 5'[Column]=_week))
VAR _end = CALCULATE(MAX('CALENDAR 5'[Date]),FILTER('CALENDAR 5','CALENDAR 5'[Column]=_week))
RETURN CONCATENATE("Reporting Period",CONCATENATE(_start,CONCATENATE("-",_end)))

[Anonymous]

Hi@Anonymous 

You're a star mate. Your code is working just perfectly.

One last thing... can I change the format of the Reporting Period dates to read as... DD/MM/YYYY?

Thanks, B

[Anonymous · Accepted solution]

@Anonymous Please use format function to change the date format

PrevWeekRange = 
VAR _week = CALCULATE(MAX('CALENDAR 5'[Column]),FILTER('CALENDAR 5','CALENDAR 5'[Date]=TODAY()))-1
VAR _start = FORMAT(CALCULATE(MIN('CALENDAR 5'[Date]),FILTER('CALENDAR 5','CALENDAR 5'[Column]=_week)),"dd/MM/yyyy")
VAR _end = FORMAT(CALCULATE(MAX('CALENDAR 5'[Date]),FILTER('CALENDAR 5','CALENDAR 5'[Column]=_week)),"dd/MM/yyyy")
RETURN CONCATENATE("Reporting Period",CONCATENATE(_start,CONCATENATE("-",_end)))

[Anonymous]

Hi @Anonymous

Thank you so much for your prompt response which has been of great help to me.

Your code works just perfect to suit my needs.

Kind regards, B

[Anonymous · Accepted solution]

CALENDAR 5 = 
VAR BASECALENDAR =
    CALENDAR ( DATE ( 2017, 12, 03 ), DATE ( 2022, 06, 30 ) )
RETURN
    GENERATE (
        BASECALENDAR,
        VAR BASEDATE = [DATE]
        VAR YEARDATE = YEAR ( BASEDATE )
        VAR MONTHNUMBER = MONTH ( BASEDATE )
        VAR MONTHNAME = FORMAT ( BASEDATE, "MMMM" )
        VAR YEARMONTHNAME = FORMAT ( BASEDATE, "MMM YY" )
        VAR YEARMONTHNUMBER = YEARDATE * 12 + MONTHNUMBER -1
        VAR WeekNumber = FORMAT(WEEKNUM(BASEDATE,2),"00")
        RETURN ROW (
            "DAY", BASEDATE,
            "YEAR", YEARDATE,
            "MONTH NUMBER", MONTHNUMBER,
            "MONTH", MONTHNAME,
            "YEAR MONTH NUMBER", YEARMONTHNUMBER,
            "YEAR MONTH", YEARMONTHNAME,
            "Year Week Number",CONCATENATE(YEARDATE,WeekNumber)
        )
    )

Calculated Column = RANKX(FILTER(ALL('CALENDAR 5'[Year Week Number]),RIGHT('CALENDAR 5'[Year Week Number],2)<>"53"),'CALENDAR 5'[Year Week Number],,ASC)-1

@Anonymous Hi 
Can you please add Year week number in your calendar table (1 st code) and then create a column to get the week number
In case you want to exclude week number 53 for each year use

YearWeekNumber =

RANKX(FILTER(ALL('CALENDAR 5'[Week Number]),RIGHT('CALENDAR 5'[Week Number],2)<>"53"),'CALENDAR 5'[Week Number],,ASC)-1