How to make calculation for customers with new orders

[Bart]

Hello, i want to make a Dax formula where i have as result a % Sales this season 24 versus last season 23. And in this calculation i only want calculate this when a customer has already places SaleOrders for this season 24. 

I have an example in excel attached: 

SalesOrder Table

You see that Customer C don't have new orders for season 24

In the calculation below i expect an outcome of 114% ( = 8/7)

[Bart · Accepted solution]

Thank you for this great solution @Ashish_Mathur , I used both 👍🏻

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[eliasayyy]

Hello @Bart ,

create a flag column

Flag = 
VAR _on = 'Table'[OrderNb]
VAR _name = 'Table'[Name]
VAR _season = 'Table'[Season]

RETURN 
IF(
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[OrderNb] <> _on &&
            'Table'[Name] = _name &&
            'Table'[Season] <> _season
        )
    ) > 0,
    1,
    0
)

this measure will count where each client have multipl sales from different seasons,

Result

now just create a simple dax measure

Varriance = 
VAR _s23 = CALCULATE(SUM('Table'[Amount]),'Table'[Season] = 23 && 'Table'[Flag] = 1)
VAR _s24 = CALCULATE(SUM('Table'[Amount]),'Table'[Season] = 24 && 'Table'[Flag] = 1)
RETURN
DIVIDE(
    _s24,
    _s23
)

results

[Bart]

Hi, the result is ok, thank you for the quick reply!

But i have a tabel with 30k rows, and when i refresh i get the error : there is not enough memory.

I guess this is due to this new column and measure. 

Do you have a solution for this?

regards, Bart