Spread target over weekly period

[paulfink]

hi guys,

I am currently formatting a montly report into a weekly period.

I have a line graph that is supposed to show current targets against sales.

My targets are dated the start of each month e.g 01/06/2020, 01/07/2020 etc.

I used CALCULATE(SUM('Table1'[Target], DATESMTD('Calendar'[Date])) / [MaxWeek]

The MaxWeek is the Max weeks in a month.

e.g 60 / 4 = 15

so every weekly date should show 15.

Currently its only showing the 15 for the start of the month but the DATESMTD is supposed to spread the amount out over the weekly period.

This is how it is supposed to be but it is only showing the target for the 27/07/2020 and 31/08/2020

I already have the weekly date which is:

Weekly Date = 
var CurrentDate=LASTDATE('Calendar Table'[Date])
var DayNumberOfWeek=WEEKDAY(LASTDATE('Calendar Table'[Date]),3)
return
DATEADD(
    CurrentDate,
    -1*DayNumberOfWeek,
    DAY)

How can i do this so the target spreads over each of the weeks.

[Greg_Deckler]

@paulfink Unclear but I *think* you want a variation of periodic revenue (reverse YTD) - https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Revenue-Reverse-YTD/m-p/373185#M111

Am I correct in stating that you have monthly figures and you want to spread those numbers over the weeks contained within those months?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[paulfink]

hi @Greg_Deckler , i tried your Periodic Revenue Measure but it did not work. The figures are incorrect and are not spreading over.

I used both my calendar table and the target table dates and they are both wrong

[Greg_Deckler]

@paulfink OK, need sample data and expected output.

[paulfink]

Does my post not have enough?

[Greg_Deckler]

@paulfink It might but I'm not sure what I am looking at. Is the table of information you supplied represent source data? Or is that expected output? Doesn't seem to be source data because it is by week. Based on the description I would have expected something like:

Month (dd/mm/yyyy), Value

1/1/2020, 60

2/1/2020, 50

3/1/2020, 65

4/1/2020, 70

And then you would want to spread that 60 across 4 weeks of January or is it 5 weeks of January since there is a partial week? Which then begs the question when it comes to March, are there 5 weeks or just 4? What happens with those partial weeks in months?

@ me in replies or I will lose the thread.