Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
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.
I created the new column Accumulate_Week_Num, but it's not working. I get strange numbers, what did I wrong?
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.
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.
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.
@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
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:
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |