Comparing daily values to start of the month value.

[Anonymous]

Hello Power BIers,

I have two tables, one is predicted for the month giving the first date of the month

1/1/2020	62
2/1/2020	55
3/1/2020	75

We dividing these values by the number of days in the month with 

Days in month = DATEDIFF(STARTOFMONTH('Date'[Date]),ENDOFMONTH('Date'[Date]),DAY) + 1

to compare daily actual values to the monthly predicted values. Because only the first of the month has data the table ends up:

Date	Predicted	Actual	Difference
1/1/2020	2	1.8	.2
1/2/2020	BLANK	1.7	-1.7
1/3/2020	BLANK	1.9	-1.9
...	...	...	...
2/1/2020	1.96	2.1	-.14

The hope is to build a measure that will compare each day to to start of the month, since the predicted values do no change by the day, or to fill out some kind of table with the missing predicted date values.

Hopefully this is clear, and thank you for the help!

[v-lili6-msft]

hi  @Anonymous 

For your case, just use TOTALMTD Function in your formula or add a new measure as below:

https://docs.microsoft.com/en-us/dax/totalmtd-function-dax

for example, I assume your Predicted measure is:

Predicted = DIVIDE(SUM('Table'[value]),DATEDIFF(STARTOFMONTH('Date'[Date]),ENDOFMONTH('Date'[Date]),DAY) + 1)

Now adjust the measure like below:

PredictedMTD = TOTALMTD([Predicted],'Date'[Date])

PredictedMTD2 = TOTALMTD( DIVIDE(SUM('Table'[value]),DATEDIFF(STARTOFMONTH('Date'[Date]),ENDOFMONTH('Date'[Date]),DAY) + 1),'Date'[Date])

Result:

 Regards,

Lin

[Anonymous]

Works great, thank you tons!