Power Bi DAX: Setting a Target for each Week of Month

[paulfink]

Hi Guys,

I have encountered a problem with my data.

I had some help with this morning but it would be best to make a new question. Here is the link to the previous question: Power Bi DAX: Divide Value and set it for each week

I asked to get a weekly target from my monthly target, needed to divide my target by amount of weeks in a month, which we have figured out. Now my data is showing only for the first of every month because the column where I have put it only has the first of each month as the date column but I need the weekly to get set on the start of each week. Currently it is set to to the first of each month.

I need to spread this to the start of each week, maybe doing the formula from my calendar table, the value for each week will be the same as the weekly target that is set to the first of each month.

There are multiple people with targets so they will all total up but each have targets for every month.

Here are my calendar table and a dummy sheet that is my desired output on the right, with my actual data at the bottom.

Dummy Sheet with Example Data of what i have

Calendar Table  

I want this in a Line and Clustered Column Chart and have the targets as Lines.

[v-lionel-msft]

Hi @paulfink ,

data model:

//calculated columns in calendar table
Weekday = WEEKDAY('calendar table'[Date],2) 
Weeknum = WEEKNUM('calendar table'[Date], 2) 
Weeknum by month = 
CALCULATE(
    COUNT('calendar table'[Weeknum]),
    FILTER(
        'calendar table',
        'calendar table'[Date].[MonthNo] = EARLIER('calendar table'[Date].[MonthNo]) 
        &&
        'calendar table'[Weekday] = 7
    )
)
Start of week = 
CALCULATE(
    MAX('calendar table'[Date]),
    FILTER(
        'calendar table',
        'calendar table'[Weeknum] = EARLIER('calendar table'[Weeknum]) && 'calendar table'[Weekday] = 1
    )
) 

//measure
Weekly Target = MAX(Sheet9[Target]) / MAX('calendar table'[Weeknum by month])

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paulfink]

@v-lionel-msft 

Here is my calender table, ive had to take out the future years.

The start of the week isnt working. I need it to show the week from the start of the month.

I know this is confusing but im trying to show weekly data for the past 8 Calendar Weeks.

[mwegener]

Hi @paulfink ,

take a look at this.

PBIX

[paulfink]

Thank you for the file @mwegener it makes senses.

but that isnt what i need. 

My start of week formula is showing December in January because it is in the same week but i need to have all the dates in the column to be month specific so only jan dates go in jan, feb in feb etc. I understand your data but it does not work for me i am sorry. How can i change my start of week formula so that it does not show any other month than the current month, please refer to my calendar to see what i mean.

Start of Week =

var CurrentDate=LASTDATE('Closed Calendar Table'[Date])

var DayNumberOfWeek=WEEKDAY(LASTDATE('Closed Calendar Table'[Date]),3)

return

DATEADD(

CurrentDate,

-1*DayNumberOfWeek,

DAY)

[paulfink]

Thank you @v-lionel-msft  but,

The Start of Week formula has made the dates are now showing 5 years ahead of the correct date. e.g 1/01/2020 is showing as 01/01/2025. Can you change your formula to fix this? im new to DAX and do not know all the functions that i can use.

[mwegener]

Hi @paulfink ,

since months and weeks are not congruent, I would spread the monthly target over days and then add up to weeks.

[paulfink]

How would i be able to do this?