topic Re: Dax Measure to Calculate Forecast in DAX Commands and Tips

Dax Measure to Calculate Forecast

Anonymous — Thu, 11 Feb 2021 21:06:53 GMT

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,

Re: Dax Measure to Calculate Forecast

Anonymous — Fri, 12 Feb 2021 07:09:30 GMT

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.

Re: Dax Measure to Calculate Forecast

rfigtree — Fri, 12 Feb 2021 09:15:13 GMT

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

Re: Dax Measure to Calculate Forecast

rfigtree — Fri, 12 Feb 2021 09:24:36 GMT

sorry small error

Re: Dax Measure to Calculate Forecast

Anonymous — Fri, 12 Feb 2021 14:32:20 GMT

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

Regards,

Re: Dax Measure to Calculate Forecast

Anonymous — Fri, 12 Feb 2021 15:29:26 GMT

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,

Re: Dax Measure to Calculate Forecast

Anonymous — Tue, 16 Feb 2021 14:48:18 GMT

Let me know If you find a solution for me.

Re: Dax Measure to Calculate Forecast

Anonymous — Tue, 16 Feb 2021 14:51:22 GMT

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

Re: Dax Measure to Calculate Forecast

Anonymous — Tue, 16 Feb 2021 18:17:05 GMT

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.

Re: Dax Measure to Calculate Forecast

Anonymous — Tue, 16 Feb 2021 19:42:30 GMT

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.