How to sum an average per day measure to monthly level?

[NielsBakker]

Hi! 

Currently, for my forecast I've created a measure that is based on the average cost of last and current month. This is an average per day and is a flat line for the future. See below the result, the dotted line is the correct forecast and is 10.627:

Now my problem is when I drill down to a monthly level in my graph, the forecast will stay at the daily level. So at the 10.267 instead of summing all the days... 

I'm using the following measure:

Forecast per day:=
VAR NumDaysPrevPeriod = DATEDIFF(EOMONTH(Today(),-2),Today(),DAY)
VAR PriorMonthCost = CALCULATE([Totals Per Cost Type (Resource)],REMOVEFILTERS(vwDimDateUsage),DATESBETWEEN(vwDimDateUsage[Date],EOMONTH(TODAY(),-2),TODAY()))

RETURN
IF(ISBLANK([Totals Per Cost Type (Resource)]),
DIVIDE(PriorMonthCost,NumDaysPrevPeriod),
Blank())

RemoveFilters is used so that the forecast is always based on current previous month and current month.

IF(ISBLANK is used so that the graph only shows the forecast for future dates.

In short, how can I make sure that my daily forecast will be summed up correctly when dilling down in the graph to a monthly level? 

Many thanks!

Kind regards,

Niels

[NielsBakker · Accepted solution]

Thanks for your response. 

In the mean time I found the solution that I needed. Needed to put a SUMX before the DIVIDE funtion. 

[Anonymous]

Hi @NielsBakker ,

For the information you provided, I don't think I can do the .pbix file recovery, if you can provide me more images, information or .pbix files, I can do a more in-depth research.
But I noticed that your DAX code utilizes the REMOVEFILTERS function, so I think you can change it to ALLSELECTED function or ISFILTERED function, the following is the documentation about the two functions, you can read it: ALLSELECTED function (DAX) - DAX | Microsoft Learn and ISFILTERED function (DAX) - DAX | Microsoft Learn

Best Regards

Yilong Zhou

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