Dax Measure to Calculate Forecast

[Anonymous]

I need Forecast Column result in Power BI through a measure. A particular sale table has the following information

1- Date

2- Amount

I created two measures

1- Total Amount - for the Amount column

2- Ratio% - for all %

Regards,

[rfigtree]

sorry small error

[rfigtree]

your description of the problem seems weird but i think this matches it.

[Anonymous]

Let me knowif you find a solution for me as per my below message.

[Anonymous]

Thank you for providing this code. Howerver I used "ratio" field in the above image as a measure instead of column. Please see the below image for ratio working.

Regards,

[Anonymous]

Hi @Anonymous 

In the image, you have specified the formula D1 * C2 ie... previous forecast value * current ratio.

but, 109,991,470 * 0.96 will return 10,55,91,811.20  not the value you have given in forecast column as 109,991,471.

[Anonymous]

Let me know If you find a solution for me.

[Anonymous]

Hi  @Anonymous 

you can try the below dax formulas as calculated columns,

1) first_non_blank_amount = CALCULATE(SUM(Sheet5[Amount]),FILTER(Sheet5,Sheet5[Date]=MIN(Sheet5[Date])))
2) except_first_ratio = IF(Sheet5[Date]=MIN(Sheet5[Date]),BLANK(),Sheet5[ratio %])
3) earlier_dates = CONVERT(IF(Sheet5[Date]=MIN(Sheet5[Date]),BLANK(),Sheet5[Date]),DATETIME)
4) cumulative product ratio = CALCULATE(PRODUCT(Sheet5[except_first_ratio]),FILTER(ALLSELECTED(Sheet5),Sheet5[earlier_dates]<=EARLIER(Sheet5[earlier_dates])))
5) forecast value = IF(ISBLANK(Sheet5[first_non_blank_amount]*Sheet5[cumulative product ratio]),
Sheet5[Amount],
Sheet5[first_non_blank_amount]*Sheet5[cumulative product ratio])
 
But I don't know how well it will work with large dataset and date column with duplicate dates.
 Also I couldn't create forecast value as a measure.

[Anonymous]

Thanks for the column coding. However, I used ratio% as a measure in my table. Please see the ratio% measure calculation in the above screenshot.

[Anonymous]

Sorry for the formula mistake in excel. Yes. you are right. Please see the new image.

Regards,