Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Running Total with duplicate dates in DirectQuery

Hello everyone,   Beware, complicated subject! (I think so at least…) The file looks like this but with multiple articles, and is imported via DirectQuery (no column or PowerQuery possible): ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Update on the struggle !

 

I have tried a new way of doing it that works on my sample file, but takes too long on the main file.

 

I set up a "CountDate" formula to give me a measure that I can compare to rank the lines :

CountDate = DATEDIFF(TODAY(),SUM(CdeClientEnCours[Date livraison]),DAY) - DIVIDE(1,sum(CdeClientEnCours[Quantité restante])) - DIVIDE(1,SUMX(CdeClientEnCours,VALUE(RIGHT(CdeClientEnCours[Numéro Commande]))))

 The DATEDIFF is here to give me a stable comparison of the different dates, and the two other DIVIDE are here to separate lines where the dates are the same.

The result looks like this :

But then I tried to do a Running total over this CountDate measure :

Cumulative = 
VAR CurrentRow = [CountDate]
RETURN
    CALCULATE (SUM('CdeClientEnCours 2'[Quantité restante]),
        FILTER(ALLEXCEPT(Articles,Articles[codeProduit]), [CountDate] <= CurrentRow) 
        
    )

There are different "codeProduit" in the file, that is why i'm using the ALLEXCEPT so it gives me the Running Total for each codeProduit.

 

But I run into a capacity problem...

 

Is there a way to ease the code for PowerBI to be capable of calculating it ?

 

Thanks in advance !