topic Re: Divide Monthly Budget by working days and accumulate in DAX Commands and Tips

Divide Monthly Budget by working days and accumulate

joshua1990 — Wed, 22 Jul 2020 17:12:32 GMT

Hello everyone!

Unfortunately, this is not the first time I am asking for help regarding this challenge.

I have a budget table that has weekly buckets on area level.

The structure is like:

Area	Year-Week	Budget
A	2020-01	1000
A	2020-02	1250
A	2020-03	1500
A	2020-04	1000
B	2020-01	1000
B	2020-02	1750
B	2020-03	1500
B	2020-04	1000

This table is linked to my calendar table through year-week key and to the item master through the area key.

So, I can determine which week budget belongs to which fiscal month.

I can also determine which sale on item level belongs to which area.

Now, how can I divide the monthly budget by the working days for the corresponding month and to accumulate these daily values?

The result should be like this for month January with working days:

Date	Budget	Running Total
01.01.20	237,5	237,5
02.01.20	237,5	475
03.01.20	237,5	712,5
04.01.20		712,5
05.01.20		712,5
06.01.20	237,5	950
07.01.20	237,5	1187,5
08.01.20	237,5	1425
09.01.20	237,5	1662,5
10.01.20	237,5	1900
11.01.20		1900
12.01.20		1900
13.01.20	237,5	2137,5
14.01.20	237,5	2375
15.01.20	237,5	2612,5
16.01.20	237,5	2850
17.01.20	237,5	3087,5
18.01.20		3087,5
19.01.20		3087,5
20.01.20	237,5	3325
21.01.20	237,5	3562,5
22.01.20	237,5	3800
23.01.20	237,5	4037,5
24.01.20	237,5	4275
25.01.20		4275
26.01.20		4275
27.01.20	237,5	4512,5
28.01.20	237,5	4750

I already have studied this blog:

https://www.daxpatterns.com/budget-patterns/

But I can't figure it out.

This is my measure to sum the working days per month

Workingdays = VAR vSelectedMonth = SELECTEDVALUE ( 'Calendar'[Fiscal Month Number]) RETURN CALCULATE ( COUNTROWS ( 'Calendar' ), FILTER ( 'Calendar', 'Calendar'[Fiscal Month Number] = vSelectedMonth && 'Calendar'[IsWorkingDay] = 1 ) )

Can someone help?

Re: Divide Monthly Budget by working days and accumulate

amitchandak — Thu, 23 Jul 2020 02:51:58 GMT

joshua1990 , In your calendar you can work day

Work day = if(WEEKDAY([Date],2)<6,1,0)

Workday of the month = sumx(filter(Date, Date[Month Year] =earlier(Date[Month Year] ),[Work day ])

Now when you create allocation using date table you can divide by work days

refer if this file can help

https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0

Re: Divide Monthly Budget by working days and accumulate

joshua1990 — Thu, 23 Jul 2020 05:19:18 GMT

amitchandak Thank you so much!

And how can I accumulate these daily targets? Like Running Totals

Re: Divide Monthly Budget by working days and accumulate

amitchandak — Thu, 23 Jul 2020 05:28:05 GMT

joshua1990 ,

Monthly

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

Quarterly

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Yealy = YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

Over all. With Date table

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Appreciate your Kudos.

Re: Divide Monthly Budget by working days and accumulate

joshua1990 — Thu, 23 Jul 2020 09:23:38 GMT

amitchandak Thanks a lot!

Does your approach work with weekly budget as mentioned in the first post?

If so, how?

I am using a fiscal calendar with individual start and end dates for every month.