Forum Discussion
Forecast by using start date and end date issues
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,
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,
KellyDid I answer your question? Mark my post as a solution!
2 Replies
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,
KellyDid I answer your question? Mark my post as a solution!