Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
I have a table of target that have values summarized by month.
The date is set to the 1st day of each month.
Target
storecode | date | target |
A | 2021/10/01 | 18000 |
A | 2021/11/01 | 16200 |
B | 2021/10/01 | 10000 |
B | 2021/11/01 | 16000 |
I also have a calendar table. If day is workday is show as 1.
Calendar
date | working day |
2021/10/01 | 1 |
2021/10/02 | 0 |
2021/10/03 | 0 |
2021/10/04 | 1 |
2021/10/05 | 1 |
2021/10/06 | 0 |
2021/10/07 | 1 |
2021/10/08 | 1 |
*Suppose sum of working day Oct 16, Nov 17
I have a slicer to select a date(year/month/date) in my report.
I would like to show monthly and daily.
If the date is not a working day, I would like to show zero.
I would like to have a result like this.
When select 2021/10-5 -2021/11/30
Sum of working day is Oct 14, Nov 17 on this date range.
Monthly
Oct-21 | Nov-21 | |
A | 15750 | 16200 |
B | 8750 | 16000 |
Daily
2021/10/5 | 2021/10/6 | 2021/10/7 | 2021/10/8 | |
A | 1125 | 0 | 1125 | 1125 |
B | 625 | 0 | 625 | 625 |
I would greatly appreciated any help!
Hi, @olives
You can try the following methods.
Month = MONTH([date])
Measure:
Daily =
Var _Sum=CALCULATE(SUM(Target[target]),FILTER(ALL(Target),MONTH([date])=MAX('Calendar'[Month])&&[storecode]=MAX(Target[storecode])))
Var _Count=CALCULATE(COUNT('Calendar'[date]),FILTER(ALL('Calendar'),[Month]=MAX('Calendar'[Month])&&[working day]=1))
Return
IF(SELECTEDVALUE('Calendar'[working day])=1,DIVIDE(_Sum,_Count),0)
Monthly = SUMX(FILTER(ALL('Calendar'),[date]>=MIN('Calendar'[date])&&[date]<=MAX('Calendar'[date])&&[Month]=MAX('Calendar'[Month])),[Daily])
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your support.😀
Is it possible to write a value on one DAX?
Because I use Filed parameter (year, month, day) to Column.
Should I use switch or is there a way not to use switch?
Hi, @olives
Can you provide more working days? Or what rules are used to distinguish whether it is a working day?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Thank you for your help.
There is no rule on working day so if "working day" filed on calendar table is 1, that is working day.
I put it on the right because it might be too long.
date | working day | date | working day |
2021/10/1 | 1 | 2021/11/1 | 1 |
2021/10/2 | 0 | 2021/11/2 | 1 |
2021/10/3 | 0 | 2021/11/3 | 0 |
2021/10/4 | 1 | 2021/11/4 | 1 |
2021/10/5 | 1 | 2021/11/5 | 1 |
2021/10/6 | 0 | 2021/11/6 | 0 |
2021/10/7 | 1 | 2021/11/7 | 1 |
2021/10/8 | 1 | 2021/11/8 | 1 |
2021/10/9 | 1 | 2021/11/9 | 1 |
2021/10/10 | 1 | 2021/11/10 | 1 |
2021/10/11 | 0 | 2021/11/11 | 0 |
2021/10/12 | 0 | 2021/11/12 | 0 |
2021/10/13 | 1 | 2021/11/13 | 1 |
2021/10/14 | 0 | 2021/11/14 | 0 |
2021/10/15 | 1 | 2021/11/15 | 1 |
2021/10/16 | 0 | 2021/11/16 | 1 |
2021/10/17 | 0 | 2021/11/17 | 0 |
2021/10/18 | 0 | 2021/11/18 | 0 |
2021/10/19 | 0 | 2021/11/19 | 0 |
2021/10/20 | 1 | 2021/11/20 | 1 |
2021/10/21 | 1 | 2021/11/21 | 1 |
2021/10/22 | 0 | 2021/11/22 | 0 |
2021/10/23 | 0 | 2021/11/23 | 0 |
2021/10/24 | 1 | 2021/11/24 | 1 |
2021/10/25 | 1 | 2021/11/25 | 1 |
2021/10/26 | 1 | 2021/11/26 | 1 |
2021/10/27 | 0 | 2021/11/27 | 0 |
2021/10/28 | 1 | 2021/11/28 | 1 |
2021/10/29 | 0 | 2021/11/29 | 0 |
2021/10/30 | 0 | 2021/11/30 | 0 |
2021/10/31 | 1 |
Thank you!
User | Count |
---|---|
101 | |
69 | |
58 | |
47 | |
47 |