Forecast by using start date and end date issues

[Anonymous]

Hi everyone,

     I created three dashboards for forecast related by using start date and end date... 

1. I created one temp table for  calender it may gets max and min of start date and end date to create all the dates in that table

2. I created measures for forecast dashboard  measure is

Total Amount =

VAR tmpCalendar = ADDCOLUMNS('Calendar date',"Month",MONTH('Calendar date'[Date]),"Year",YEAR('Calendar date'[Date]),"MonthYear",VALUE(YEAR('Calendar date'[Date]) & FORMAT(MONTH('Calendar date'[Date]),"0#")))

VAR tmpTotal = ADDCOLUMNS(Tests,"MonthYearBegin",VALUE(YEAR(Tests[StartDate]) & FORMAT(MONTH(Tests[StartDate]),"0#")),

"MonthYearEnd",VALUE(YEAR(Tests[EndDate]) & FORMAT(MONTH(Tests[EndDate]),"0#")))

VAR tmpTable =

SELECTCOLUMNS(

FILTER(

GENERATE(

tmpTotal,

SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])

),

[MonthYear] >= [MonthYearBegin] &&

[MonthYear] <= [MonthYearEnd]

),

"Total",Tests[Total],

"Count of Days",Tests[Days in Month],

"Remaining Days",Tests[Duration of Dayys]

)

RETURN SUMX(tmpTable,([Total]/[Count of Days])*[Remaining Days])

It succesfully works for me ..In that count of days is no of days in a month and remaining days is duration of days in a month for ex 21-01-2020 to 31-012020 duration of days is 11 like that 

After that I want to create another dashboard that is year wise total that measure is

Year Total = SUMX(Tests,(Tests[Total]/Tests[Days in Year])*Tests[Duration of Dayys])

this also working good

but I tried to show the sum of all the months in the forecast dashboard is divided by 12 thats not showing correct total..

that measure is 

Sum of all months = SUMX(Tests,Tests[Total Amount]/12)

I stucked on that measure. kindly help me as soon as possible

I attached my sample pbix file for your reference

https://1drv.ms/u/s!Aoy7ZnpipsSQas2l9s3Xn_smfIM?e=eobqj8 

Advance thanks to all,

[Anonymous · Accepted solution]

Hi @Anonymous ,

Modify your measure as below:

_Sum of all months = IF(ISINSCOPE('Calendar date'[Date].[Month]),Tests[Total Amount]/12,SUMX(ALLSELECTED('Calendar date'[Date]),Tests[Total Amount]/12))

And you will see:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@Anonymous 

Thanks a lot for your help