cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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-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!

4 REPLIES 4
Community Support

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.

Helper I

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?

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.

Helper I

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!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors