Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BYENER
Helper V
Helper V

Ignore the whole week if there is holiday in that week

Hi All,

 

I have a serarate table with holidays and another table where I have some volumes. I have a measure where I calculate the total volume of the last 5 weeks, but I want to ignore the whole week if there is holiday.

Example week 2020-51 has two holidays 25/12/2020 and 26/12/2020. I am calculating the last 5 weeks from week 53/1 then my calculation doing a sum of week 52/51/50/49/48 , but I want this: sum of week 52/50/49/48/47

this is my measure:

X weeks sum total =
CALCULATE(
SUM('X per week'[X Carrier])+SUM('X per week'[X Retail]),
DATESINPERIOD('Date'[Date],MAX('Date'[Date])-7,-35,DAY)
)

BYENER_0-1612959336198.png

Thanks a lot.

1 ACCEPTED SOLUTION

Hi @BYENER 

I think your 1/53 contains 3 days 2021/01/01-2021/01/03, they should in the same week as 2020/12/28-2020/12/31. So I divide 2021/01/01-2021/01/03 into the 2020-53.

Update:

NewYear = IF('Date'[Year]=2020,2020,IF('Date'[Year]=2021&&'Date'[Accumulate_Week_Num]=53,2020,2021))
New_WeekNum = 
IF('Date'[NewYear] = 2021,'Date'[Accumulate_Week_Num]-53,'Date'[Accumulate_Week_Num])
Year_WeekNum = 'Date'[NewYear]&"-"&FORMAT('Date'[New_WeekNum],"00")

Build a slicer table:

Slicer = VALUES('Date'[Year_WeekNum])

Filter Measure:

Measure = 
VAR _Select = SELECTEDVALUE(Slicer[Year_WeekNum])
VAR _Accumulate = CALCULATE(MAX('Date'[Accumulate_Week_Num]),FILTER(ALL('Date'),'Date'[Year_WeekNum]=_Select))
VAR _Last5weeks = _Accumulate-5
VAR _holidayweek = CALCULATETABLE(VALUES('Date'[Accumulate_Week_Num]),FILTER(ALL('Date'),'Date'[Accumulate_Week_Num]<_Accumulate&&'Date'[Accumulate_Week_Num]>=_Last5weeks),FILTER(ALL(Holiday),Holiday[bank holiday]<>BLANK()))
VAR _Countholidayweek = COUNTAX(_holidayweek,[Accumulate_Week_Num])
VAR _NewLast5weeks = _Last5weeks-_Countholidayweek
Return
IF(MAX('Date'[Accumulate_Week_Num])>=_NewLast5weeks&&MAX('Date'[Accumulate_Week_Num])<_Accumulate&&NOT(MAX('Date'[Accumulate_Week_Num]) IN _holidayweek),1,0)

Build a matrix visual like yours and add my measure into Filter. Set it to show items when value =1.

I think your 2021-01 should start by 2021/01/04.

When I select 2021-01, it will show last three weeks without holiday,2020-47 to 2020-51.

2020-52 has chirstmas and 2020-53(2020/12/28-2021/01/03) has new year.

1.png

You can download the pbix file from this link: Test2

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

View solution in original post

14 REPLIES 14
v-rzhou-msft
Community Support
Community Support

Hi @BYENER 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem.

 

Best Regards,

Rico Zhou

Hi @v-rzhou-msft ,

 

Thanks a lot! I will try the solution Today and will accept as solution if it's working.

v-rzhou-msft
Community Support
Community Support

Hi @BYENER 

It seems that there is something wrong in your weeknum.

I think the weeknum of 2020/12/28 should be 53 instead of 52.

You seem want to let the weeknum of 2021/01/04 to be 1, so all weeknums -1.

I build a new date table.

WeekNum = WEEKNUM(Holiday[Date],2)
Year = YEAR(Holiday[Date])
Accumulate_Week_Num = 
IF (
    RANKX ( Holiday, Holiday[YEAR],, ASC, DENSE ) <> 1,
    SUMX (
        SUMMARIZE (
            FILTER ( Holiday, Holiday[YEAR] < EARLIER ( Holiday[YEAR] ) ),
            Holiday[Year],
            "Maxweeknum-1",
                MAXX (
                    FILTER ( Holiday, Holiday[YEAR] = EARLIER ( Holiday[YEAR] )),
                    Holiday[WeekNum]
                ) - 1
        ),
        [Maxweeknum-1]
    ) + Holiday[WeekNum],
    WEEKNUM ( Holiday[Date], 1 )
)
New_WeekNum = 
IF(Holiday[Year] = 2021,Holiday[Accumulate_Week_Num]-53,Holiday[Accumulate_Week_Num])
Year_WeekNum = Holiday[Year]&"-"&Holiday[New_WeekNum]

1.png

Then build a new Year week table for slicer.

Table = VALUES(Holiday[Year_WeekNum])

My Sample:

2.png

Build a measure:

Measure = 
VAR _Select = SELECTEDVALUE('Table'[Year_WeekNum])
VAR _Accumulate = CALCULATE(MAX(Holiday[Accumulate_Week_Num]),FILTER(ALL(Holiday),Holiday[Year_WeekNum]=_Select))
VAR _Last5weeks = _Accumulate-5
VAR _holidayweek = CALCULATETABLE(VALUES(Holiday[Accumulate_Week_Num]),FILTER(ALL(Holiday),Holiday[Accumulate_Week_Num]<_Accumulate&&Holiday[Accumulate_Week_Num]>=_Last5weeks&&Holiday[Holiday]<>BLANK()))
VAR _Countholidayweek = COUNTAX(_holidayweek,[Accumulate_Week_Num])
VAR _NewLast5weeks = _Last5weeks-_Countholidayweek
Return
CALCULATE(SUM('X per week'[X Carrier])+SUM('X per week'[X Retail]]]),FILTER(ALL(Holiday),Holiday[Accumulate_Week_Num]<_Accumulate&&Holiday[Accumulate_Week_Num]>=_NewLast5weeks&&NOT(Holiday[Accumulate_Week_Num] in _holidayweek)))

Result is as below.

3.png

When you select 2021-1, you will get the sum of 53,51,50,49,48.

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

 

Hi @v-rzhou-msft 

 

I created the new column Accumulate_Week_Num, but it's not working. I get strange numbers, what did I wrong?

BYENER_0-1613640597208.png

It's counting also in the new year

 

Hi @BYENER 

The screenshot is blurry. Could you share a sample with me by your Onedrive for Business?

And show me a new screenshot of the result you want.

This will make me easier to understand your problem.

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

@v-rzhou-msft ,

 

Please see Test pbix  for test pbix.
I have two tables one the volumes per day and a table with the week volumes. I calculate first the sum of the last 5 weeks and another measure the sum of the last 5 weeks per weekday. I divide these measures to get an percentage (% retail [measure]). 

I want to add a criterea in the measure [sum last 5 weeks] and [Carrier Last 5 weeks DaySUm]. This measures should skip the whole weeks when there is a holiday in the calculation.

 

I hope that my explanation is a little bit understandable.

 

Kind regards,

B. Yener

Hi @BYENER 

I think your 1/53 contains 3 days 2021/01/01-2021/01/03, they should in the same week as 2020/12/28-2020/12/31. So I divide 2021/01/01-2021/01/03 into the 2020-53.

Update:

NewYear = IF('Date'[Year]=2020,2020,IF('Date'[Year]=2021&&'Date'[Accumulate_Week_Num]=53,2020,2021))
New_WeekNum = 
IF('Date'[NewYear] = 2021,'Date'[Accumulate_Week_Num]-53,'Date'[Accumulate_Week_Num])
Year_WeekNum = 'Date'[NewYear]&"-"&FORMAT('Date'[New_WeekNum],"00")

Build a slicer table:

Slicer = VALUES('Date'[Year_WeekNum])

Filter Measure:

Measure = 
VAR _Select = SELECTEDVALUE(Slicer[Year_WeekNum])
VAR _Accumulate = CALCULATE(MAX('Date'[Accumulate_Week_Num]),FILTER(ALL('Date'),'Date'[Year_WeekNum]=_Select))
VAR _Last5weeks = _Accumulate-5
VAR _holidayweek = CALCULATETABLE(VALUES('Date'[Accumulate_Week_Num]),FILTER(ALL('Date'),'Date'[Accumulate_Week_Num]<_Accumulate&&'Date'[Accumulate_Week_Num]>=_Last5weeks),FILTER(ALL(Holiday),Holiday[bank holiday]<>BLANK()))
VAR _Countholidayweek = COUNTAX(_holidayweek,[Accumulate_Week_Num])
VAR _NewLast5weeks = _Last5weeks-_Countholidayweek
Return
IF(MAX('Date'[Accumulate_Week_Num])>=_NewLast5weeks&&MAX('Date'[Accumulate_Week_Num])<_Accumulate&&NOT(MAX('Date'[Accumulate_Week_Num]) IN _holidayweek),1,0)

Build a matrix visual like yours and add my measure into Filter. Set it to show items when value =1.

I think your 2021-01 should start by 2021/01/04.

When I select 2021-01, it will show last three weeks without holiday,2020-47 to 2020-51.

2020-52 has chirstmas and 2020-53(2020/12/28-2021/01/03) has new year.

1.png

You can download the pbix file from this link: Test2

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

@v-rzhou-msft  thanks for your good help. The week calculation is working, thanks for that. You are using a slicer in your solution, but I don't want use that. I want always show the whole table with all weeks. 

amitchandak
Super User
Super User

@BYENER . Partial solution is to create a calendar like this and use that

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

 

example

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])))
Last work day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])-1))

 

Last 7 work day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]>=max('Date'[Work Date cont Rank])-7 && 'Date'[Work Date cont Rank]<=max('Date'[Work Date cont Rank])))

Hi @amitchandak ,

 

Thanks for your reply. This is working for the holidays, but I want to ignore the the whole week if there a at least one holiday in that week.

 

How is this possible?

 

Thanks

@BYENER 

Maybe you can create a calculated column with counter with 0 or 1 and if holiday exist then calculated column should have 1. After that you can do your calculation on rows that have flag = 0.

@SpiroswayGR  this is working good, but I have to add a extra criteria in my formula. I have the following formula:

 

CALCULATE(SUM('X'[X Carrier])+SUM('X'[X Retail]),
DATESINPERIOD('Date'[Date],MAX('Date'[Date])-7,-35,DAY),'Holiday calendar'[Flag]=0)

For example in week 52(Christmast) and 53(New year) there were 2 weeks with holidays. When I use this calculation than I calculate for week 1 the sum of week 51,50 and 49. But is should sum 51,50,49,48 and 47.  I want always calculate 5 weeks and if there is a holiday week there than it should skip that.

IF the week contains a holiday than it should add +7 on the standard -35 (5weeks) in the formula.

@BYENER , You need to have a flag that populate the same value in week 

 

a new column like  if(Sumx(filter(Date, [Weekyear] = earlier([Weekyear])),[Work Day]) >0,1,0) 

 

then day rank of week rank in the same line I suggested in the blog. Do not include week with flag 1 

 

Also, refer 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.