cancel
Showing results for
Did you mean:  Helper IV

## 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)
) Thanks a lot.

1 ACCEPTED SOLUTION  Community Support

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

14 REPLIES 14  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  Helper IV

Hi @RicoZhou ,

Thanks a lot! I will try the solution Today and will accept as solution if it's working.  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]`` Then build a new Year week table for slicer.

``Table = VALUES(Holiday[Year_WeekNum])``

My Sample: 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. 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.  Helper IV

I created the new column Accumulate_Week_Num, but it's not working. I get strange numbers, what did I wrong?  Helper IV It's counting also in the new year  Community Support

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.  Helper IV

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  Community Support

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. 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.  Helper IV

@RicoZhou  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.  Super User IV

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

Proud to be a Super User!  Helper IV

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  Helper V

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.  Helper IV

@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.  Super User IV

@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

Proud to be a Super User!  Announcements #### Welcome to the User Group Public Preview  