topic Re: Display Current Week Number 99 starting from 03 December 2017 week starting on Monday to Sunday. in DAX Commands and Tips

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

Anonymous — Mon, 04 Nov 2019 07:53:16 GMT

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

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

Anonymous — Wed, 06 Nov 2019 02:49:34 GMT

Anyone..!

Thanks,

Bhoga

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

Anonymous — Wed, 06 Nov 2019 06:00:52 GMT

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

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

Anonymous — Wed, 06 Nov 2019 06:06:12 GMT

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

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

Anonymous — Thu, 07 Nov 2019 06:58:50 GMT

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

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

Anonymous — Thu, 07 Nov 2019 09:56:09 GMT

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

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

Anonymous — Fri, 08 Nov 2019 04:36:28 GMT

Hi Anonymous

Thanks a million for the timely help, greatly appreciated.

Kind regards, B

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

Anonymous — Thu, 28 Nov 2019 03:07:36 GMT

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

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

Anonymous — Thu, 28 Nov 2019 04:09:56 GMT

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

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

Anonymous — Thu, 28 Nov 2019 05:24:12 GMT

HiAnonymous

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

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

Anonymous — Thu, 28 Nov 2019 06:18:17 GMT

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

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

Anonymous — Mon, 02 Dec 2019 01:01:46 GMT

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