Month Forecast value based on first day of porfit.

[Arvind_123]

Hi Team,

I have created Dax to perform month forecast value to know how much profit will make from current profit at the end of month.

firstly I have created Average profit
Avg Profit = AVERAGEX(VALUES(DimDate[Date]),sum(Table[Profit]))

then

Month Forecast = [Avg Profit]*30

But whenever the month have 30days at then end the current profit = Month Forecast but when Month have 31 or 28 days it will not equals to current month profit due to I have used number 30.

Please help me to write a dax which can also provide me current profit = month forecast where days 31 or 28 or 29 in particuar month.

you can see in the image April month have same value as month forecast but in case of 31 days or 28 days it won't same as Month Forecast... But I need at the end of month January Profit = Month forecast profit.

It is more helpful if I get perfect dax for year forecast too .

Regards,

Arvind

 

[Arvind_123]

Hi@rsbin ,

Thank you it works perfectly for me.

Regards,

Arvind

[rsbin · Accepted solution]

@Arvind_123 ,

You can use your DimDate table to calculate the number of days in the month.  So something like this:

DaysinMonth = CALCULATE( MAX(DIM_Date[DayOfMonth] ))  //Assumes you have a DayofMonth column in your DimDate table.
Then
Month Forecast = [Avg Profit] * [DaysinMonth]

You should add the Year as well into your table visual if you will need to capture Leap Years.

Hope this gets you headed in the right direction.

Regards,