Power Bi DAX: Divide Value and set it for each week

Question

I have a target value of 20 for January but it is 20 for the month, i need to show this over each week. I have to divide the target by 4 if there are 4 weeks in a month and by 5 if there are 5 weeks in a month. It is as simple as that, i am using a line and clustered column chart to display my data, i need the target spread out into each week of the month. I also need another field to do this but im sure i can replicate your formula and make it applicable.

I have added a WeeksinMonth column that counts how many weeks are in a particular month e.g January has 5 weeks and February has 4 weeks. I need an IF statement that will divide the target and value by how many weeks in a month. e.g if month has 5 weeks then divide target and value by 5, if month has 4 weeks divide target and value by 4.

I have a calendar table with week values which i can used to put the divided target into and also the desired output i made in excel (See attached).

How will i go about this?

Desired Output

Calendar Table

Can you please show us what the end result needs to look like? — Aldert
I have edited the question as i had a change of thought of how to do this by dividing by how many weeks in a month. — user12504122
I do not understand how your data lines up. In the desired output you talk about the 27th of Jan but in your calendar table, this is only week 4? — Aldert
if you put up both the calendar table and the desired output. In the calendar table there is a Start of Week column, those dates in the column is the date of the start of the week for each month. The value and target must be evenly divided out so that there is a value for each week. I have just noticed i put Jan 2000 in instead of Jan 2020, apologies — user12504122
I understand how you want the numbers divided, but: You state in your desired output that 27 jan is the start of the week, this is a thursday? In your calendar table, 31 jan is week 5. I need to understand what is correct to be able to help you.. — Aldert

Aldert Aldert · Accepted Answer · 2020-02-17T10:30:47

enter code hereYou can create an extra column in your calendar table:

WeekMax = 
var stOfMonth = Weeks[Start of Week].[Month]
var stOfYear = Weeks[Start of Week].[year]
return CALCULATE(max(Weeks[Weekinmonth]);FILTER(Weeks;Weeks[Start of Week].[Month] = stOfMonth && Weeks[Start of Week].[Year] = stOfYear))

Make sure you have a relation in your model between calendar and target date. Now you can use this number for that week/date to divide by.

You can now add an extra column in your target table:

WeeklyTarget = Targets[Target]/Related(Calendar[WeekMax])

Power Bi DAX: Divide Value and set it for each week

1 Answers