Anonymous

Not applicable

6 years ago

Solved

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

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

Need Help

Anonymous

6 years ago

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

6 years ago

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
6 years ago
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

6 years ago

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

6 years ago

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)))

11 Replies

Anonymous

Not applicable

6 years ago

Anyone..!

Thanks,

Bhoga

Anonymous

Not applicable

6 years ago

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

Not applicable

6 years ago

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
Not applicable
6 years ago
HiAnonymous
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

Forum Discussion

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

11 Replies

Related content

User Group Leader Get Started Guide

Lakehouse Cold Start

Cold Start in Recommendation Models

DAX Starts with

Getting started with data science