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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
olives
Helper I
Helper I

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

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-21Nov-21
A1575016200
B875016000

Daily

 2021/10/52021/10/62021/10/72021/10/8
A1125011251125
B6250625625

 

I would greatly appreciated any help!

4 REPLIES 4
v-zhangti
Community Support
Community Support

Hi, @olives 

 

You can try the following methods.

Month = MONTH([date])

vzhangti_0-1680505495864.png

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

vzhangti_2-1680505588161.png

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.

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

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.

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

dateworking daydateworking day
2021/10/112021/11/11
2021/10/202021/11/21
2021/10/302021/11/30
2021/10/412021/11/41
2021/10/512021/11/51
2021/10/602021/11/60
2021/10/712021/11/71
2021/10/812021/11/81
2021/10/912021/11/91
2021/10/1012021/11/101
2021/10/1102021/11/110
2021/10/1202021/11/120
2021/10/1312021/11/131
2021/10/1402021/11/140
2021/10/1512021/11/151
2021/10/1602021/11/161
2021/10/1702021/11/170
2021/10/1802021/11/180
2021/10/1902021/11/190
2021/10/2012021/11/201
2021/10/2112021/11/211
2021/10/2202021/11/220
2021/10/2302021/11/230
2021/10/2412021/11/241
2021/10/2512021/11/251
2021/10/2612021/11/261
2021/10/2702021/11/270
2021/10/2812021/11/281
2021/10/2902021/11/290
2021/10/3002021/11/300
2021/10/311  

 

Thank you!

Helpful resources

Announcements
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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

Top Solution Authors