How to display which Product sales exceed forecast based on - first date when sales exceed forecast

[queryuser]

Dear Community,

You could know the answer to this question.

I need a card to display which product exceeds forecasted sales based on the date it happened.

Below you can see the calculations made to get the first date when either of products exceeds demand. (this works)

The issue is that:

1. product names are colums 

2. there are more products C, D, E etc.(which are out of scope) and either Product A or B needs to show up

Many thanks in advance!

Kind regards

[v-chenwuz-msft · Accepted solution]

Hi @queryuser ,

I create some example data to show your problems. And i used the following code which you can refer it in the pbix which in the end.

Card 1 sales exceeds forecasted =
VAR _A =
    SUMMARIZE( ALL( 'sales' ), [Product], "SUMP", SUM( sales[Sales] ) )
VAR _B =
    ADDCOLUMNS(
        _A,
        "IF", IF( [SUMP] >= SELECTEDVALUE( forecast[forecasted sales] ), 1, BLANK() )
    )
RETURN
    CALCULATE( MIN( 'sales'[date] ), FILTER( _B, [IF] = 1 ) )

Result:

If you need more help please share some example data.

Best Regards

Community Support Team _ chenwu zhu

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

[queryuser]

Hi _ chenwu zhu,

Thank you for your reply. The difference is that the table in Power BI is as displayed.

Prod A / Prod B as columns - in your .pbix the 'Sales' is in one culmn

How would you combined those columns into 1 ?

[v-chenwuz-msft]

Hi @queryuser ,

You mean you want to change multiply colums to one column?

---->>>

You can use unpiovt columns function ( in power query editor).

Best Regards

Community Support Team _ chenwu zhu

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

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.