Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
sorry small error
your description of the problem seems weird but i think this matches it.
Let me knowif you find a solution for me as per my below message.
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,
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.
Let me know If you find a solution for me.
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.
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.
Sorry for the formula mistake in excel. Yes. you are right. Please see the new image.
Regards,