Forecast in KPI Visual does not work, if not sold every day

[AKFM]

Hello guys,

I would like to create a KPI visual in PowerBI Desktop that forecasts the current sales of a product group for the end of the year and compares it with the annual target.

The DAX formula is quite simple:
(([sales in tons YTD]/[working day number in year])*[working days in year])
The formula works, if I simply display it as a number, the result is correct.

Problem: As soon as this product group has not been sold on one day, the KPI visual (as well as the table visual) shows a 0 instead of the correct number.

With each unsold day, the forecast should decrease slightly because the number of remaining days in the year decreases, as you can see in the screenshot. On 12.01. there were still 78 tons forecast, since then no more sales, so the single visual shows 70 tons. In the KPI visual or in the table, however, it simply shows 0.

Best Regards

[AKFM · Accepted solution]

It works since I changed the date source of the KPI visual. Now I have the date directly from the invoice table and no longer the date from the date table. The date table was properly marked as a date table and linked to the invoice table.

[v-nuoc-msft]

Hi @AKFM 

Based on your description, it seems that your KPI visual is showing 0 because the measure you are using for the indicator is blank when there are no sales for a day. A possible solution is to use a measure that returns 0 instead of blank when there are no sales, such as:

Forecast = 
IF ( 
    ISBLANK([Sales in tons YTD]), 
    0, 
    ([Sales in tons YTD] / [Working day number in year]) * [Working days in year])

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AKFM]

Hi @v-nuoc-msft 

Thanks for your answer.
Unfortunately, this makes no difference, because the ISBLANK is already included in my [Sales in tons YTD] DAX.
[Sales in tons YTD] is also not empty if there were no sales on one day, because [Sales in tons YTD] is the total to date.

On the screenshot you can see the [Sales in tons YTD], that is the 3 t, so it is not empty. The DAX forecast itself also shows the correct result, which is 70 tons in the screenshot. Only in combination with a date does it not seem to work (KPI, Table Visual).

Best regards