PowerBI Measure grab a value from STARTOFMONTH?

Question

I have a monthly forecast QTY that I'd like spread out into days for the purpose of a line chart.

I have a date table which is broken out into days. As for my forecasts table, it is structured with:

• Month (in M/1/YYYY format)
• QTY

So my hope was doing something like:

Daily FCST = 
DIVIDE(
    CALCULATE(
      SUMX(Forecasts, Forecasts[Month] = STARTOFMONTH(Dates[Date])), 
      Forecasts[QTY]
    ),
    DAY(CALCULATE(EOMONTH(LASTDATE(Dates[Date]),0)))
)

This NEARLY works, however it only returns a value for the 1st of the month. Other days of the month are null.

Answer 1

I have found the solution!

The trick was that I needed to use the DATEADD function in order to shift my date table backwards by a different number of days, each day day.

In the below code, the shiftedDates variable is a date table that's shifted backwards X number of days, where X is the day of the month - 1. So for example if the date we're checking is the 17th, then shiftedDates will have been shifted backwards 16 days. Thus causing the 17th to align to the 1st.

Daily FCST =

var dateToCalculate = CALCULATE(FIRSTDATE(Dates[Date]))
var shiftedDates = DATEADD(Dates[Date], -(DAY(dateToCalculate)-1), DAY)
var numOfDays = DAY(EOMONTH(Dates[Date])

return DIVIDE(CALCULATE(SUM(Forecasts[QTY]), shiftedDates), numOfDays)