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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.