Running Total from a "fixed" daily value

[mwc]

Greetings,

I have a monthly unit forecast value for each month.  I have a formula which counts the number of days for each month, then I find the "daily forecast" value.  Because this is a fixed value, only associated with the month, I am having trouble figuring out how to "monthalize" it to get a running total for daily forecast.  I know it would just be a straight line, but I would like it to compare against daily sales.

Here is the available data I have:

Here is the expected result:

I have the sample PBIX file, but I'm not sure how to attach it here.

[mwc · Accepted solution]

I created a probably overly complicated workaround.  Here are my steps in case anyone shows up looking to do the same:

1. I created a custom column in the query editor in each - Monthly table and Yearly table called "YearMonth"

YearMonth = Text.From(Date.Year([Date]))&Text.From(Date.Month([Date]))

2. Using these matching "YearMonth" columns, I merged the Monthly table into the Daily table.  The result of this is that my monthly forecast column is repeated for every daily row in my new merged table.

3. I created a calculated column  "Day of Month Ratio" = Date.Day([Date]) / Date.DaysInMonth([Date]) - this will allow me to multiply the Day of Month Ratio by the Monthly Forecast for each day.

Using these fields I can take the Monthly Forecast and show it as a daily cumulative value in the table.

[amitchandak]

@mwc ,You can try like this

Cumm  = CALCULATE(SUM(forecast[forecast value]),filter(all(forecast),forecast[month] <=max(forecast[month])))

 As month is in incremental format

[mwc]

I think I could use your approach if I had daily forecast listed in the table for each day.

I created a date table, then I created added a column which pulls in my Daily Forecast measure, but of course it only comes over as the first day of the month (because my monthly forecast table only has one day for each month - because it is a monthly figure)

Here is my measure for Daily Forecast:

Daily Forecast =

sum('Monthly Forecast'[Units])

/

[Days in Month]

How can I populate this table to show Daily Forecast for each day in my date table?

[mwc]

@amitchandak 

I performed your calculation, but it generates what to me looks like a rolling YTD cumulative line.  When I chart your formula on the right, I see that it will show cumulative throughout the year, but if I filter to show the current month, the result is a static value for the entire month. 

I am looking for something that shows forecast in daily units cumulative through the individual month to compare daily sales within the individual month.