Running Total with duplicate dates in DirectQuery

[Duroma]

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):

The columns in yellow on the right are columns I added on my test-file; I cannot recreate them in DirectQuery.

Basically, I want to calculate the Running Total from the column “Quantité restante”, based on “Date livraison”, for each “Article”. Here is my formula:

RunningTotal =
CALCULATE( SUM(CdeClientEnCours[Quantité restante]),
FILTER( 
ALLEXCEPT('CdeClientEnCours', CdeClientEnCours[Article]),
CdeClientEnCours[Date livraison]<= EARLIER(CdeClientEnCours[Date livraison])
))

Problem is, as you can see on the screen with the 19/05/2021, there are some duplicate dates, and the Running Total adds them together, which I don’t want him to. I would like to have 1, 2, 3, 6, 10 etc etc…

So after struggling a while I tried to give the dates a distinct ranking via RANKX :

Test_Index2 = RANKX(ALL(CdeClientEnCours),CdeClientEnCours[Date livraison],,ASC,Dense)

But I can only use a measure and it gives me this message:

“It is impossible to determine a unique value for column “Date Livraison” from table blablabla. This can occur when a formula or measure references a column with many values, without specifying an aggregation as min, max, count, and sum to get a unique result.”

I also tried this : https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/

But it requires converting the date column to an integer column, and I haven’t found how to do that with a measure.

Thanks a lot in advance for your help !

Cheers,

Romain

[Duroma]

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 !

[amitchandak]

@Duroma , Try like

RunningTotal =
CALCULATE( SUM(CdeClientEnCours[Quantité restante]),
FILTER(
allselected('CdeClientEnCours'),
CdeClientEnCours[Date livraison]<= max(CdeClientEnCours[Date livraison]) +rand()/100
))

or refer

Rank Tie breaker
https://community.powerbi.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-Multiple-...
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/

[Duroma]

@amitchandakThanks for your answer !

I tried your formula, but it still does tie on the same date :

I looked on the posts you linked, but I run in the second issue I mentioned :

To do multiple columns ranking, I need to compare the dates with other columns somehow, using an integer. But the column "Date de livraison" is in Date format, and needs to be because it is linked to a Calendar table.

I can't do a ranking of the Dates via RankX because it shows me the error :

“It is impossible to determine a unique value for column “Date Livraison” from table CdeClientEnCours. This can occur when a formula or measure references a column with many values, without specifying an aggregation as min, max, count, and sum to get a unique result.”

I think the solution lies around this problem, but i'm quite new to PowerBI and I don't know how I can overpass this issue of converting the Date to an integer inside a Measure.

Thanks in advance for your help !