How to divide a monthly summary by the number of working days and display it by working day

[olives]

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!

[v-zhangti]

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.

[olives]

@v-zhangti 

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?

[v-zhangti]

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.

[olives]

@v-zhangti 

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!