aggregate weekly data allocated per workday into monthly total

[AndrevanDijk]

I have data where amount is on weekly basis (date on friday) which I'd like to convert into monthly values, but in several weeks the month changes over to the next

I use a calendar with yearweek and yearmonth columns and created custom yearweek and yearmonth columns in WeeklyData table

I create daily values based on the week value which work accurately:

dailyamount =

VAR weekdays =  CALCULATE(COUNTROWS('Calendar'),  VALUE('Calendar'[WorkingDay]) = 1,All('Calendar'),Values('Calendar'[YearWeek] ))

VAR weeklyamount =  CALCULATE(SUM(WeeklyData[Amount]),TREATAS(Values('Calendar'[YearWeek]),WeeklyData[YearWeek]))

RETURN

IF(ISFILTERED(Calendar[Date]),

DIVIDE(weeklyamount,weekdays,0),weeklyamount)

I try to do the same trick but it doesn't workout, I can't use TREATAS(Values('Calendar'[YearMonth]),WeeklyData[YearMonth]) to sum the measure dailyamount

monthlybydailyallocation =

VAR weekdays =  CALCULATE(COUNTROWS('Calendar'),  VALUE('Calendar'[WorkingDay]) = 1,All('Calendar'),Values('Calendar'[YearWeek] ))

VAR weeklyamount =  CALCULATE(SUM(WeeklyData[Amount]),  VALUE('Calendar'[WorkingDay]) = 1,All('Calendar'),TREATAS(Values('Calendar'[YearWeek]),WeeklyData[YearWeek]))

VAR dailyamount = IF(ISFILTERED(Calendar[Date]),DIVIDE(weeklyamount,weekdays,0),weeklyamount)

RETURN

SUMX(FILTER('Calendar','Calendar'[WorkingDay]=1),dailyamount)

anyone knows the trick?

sample file: WeeklyToMonthly.pbix

[AndrevanDijk]

Hi @danextian,

totally different but interesting approach, I'm not that experienced with M and also a Pbi newbie. Your expample producing different values than my expectation is because  the table calculates forward from the given data date, this should be backwards: Aug 2nd value should be split to Aug 1, July 31,30 and 29. That's  why your sample also is not giving the right monthly values. The dates in week 31 should have 6 per day

But how about in M dealing with workdays, then weekvalue needs to be divided by the workdays in that particular week and the non workday should get a value?

[danextian · Accepted solution]

Reversing the week split is as simple as changing a part of the code from {0..6} to {-6..0} which creates a list of numbers from -6 to 0 and which can be expanded into rows and added to the week date.

Our monthly totals are the same except for June which in mine shows 20 while yours shows 12.  Both 6/21 and 6/28 at 10 each should belong to June thus 20.

And then I've realized you added a filter to include only the dates on or after 6/21. Anyway, please see the attached file.

[danextian]

Hi @AndrevanDijk ,

Instead of writing a complex DAX, I would just generate a table in M that has a separate row for each weekday and the  daily budget.

I am just unsure how computed those amounts as mine shows differently

Please see attached sample pbix.