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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.