Issue with quantity difference between two dates when sorting by date descending

[Poloscopie]

Hello dear PBI community,

I have an issue with a metric on a report, which calculates the difference of a quantity between two snapshots. This issue occurs when I change the way the snapshots are sorted.

The delta is calculated this way:

Qty Δ D-1 =

    VAR PreviousPhoto =

        CALCULATE(

            SUM('Evolution of Qty'[Qty]),

            PREVIOUSDAY('Evolution of Qty'[Photo])

        )

    RETURN

        IF(

            ISBLANK(PREVIOUSDAY('Evolution of Qty'[Photo])) , BLANK(),

            SUM('Evolution of Qty'[Qty]) - PreviousPhoto

        )

Originally, the formula works as we can see on the screenshot below. The "Qty Δ D-1" returns indeed for each category if we have more or less quantity than the day before.

My concern now is to display the matrix in a different order: I need the dates to be displayed from the newest to oldest (so in the example, from December 2nd to November 28th). But if it's possible to sort a chart by axe, it's not possible to sort a matrix by column.

So I created an extra column making the difference between the current date and the photo date in order to use this column to sort the Photo column with it.

But this sorting, even if the columns order is now OK, my metric is now broken, as displayed below:

The measure returns the quantity value, meaning that the PreviousPhoto var always = 0.

Trying to change PREVIOUSDAY by NEXTDAY does not fix the function. Actually, we can see that DAX still interprets PREVIOUSDAY well as the value for the oldest photo date returns a blank value.

I don't know how to fix this, nothing seems to work.

[lbendlin]

Your data model is missing a calendar table - those are mandatory if you want to use time intelligence functions.

[Poloscopie]

Hello Ibendlin, thanks for your answer.

Unfortunately, having a calendar table doesn't seem to help me here, I actually tried and doesn't see any solution with it.

The only table on this job is this one:

Even worse, if I try to create a "descending sorting column" in my calendar table, the action of sorting the Date column by this new column is impossible as it creates a circular dependency.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Poloscopie]

Hello Ibdendlin,

You'll find the report right here.

The expected result would be as below in term of calculation: the delta D-1 is what is the difference between D0 and D-1 data (based on the snapshot date in column), but the sorting of the column values should be from the most recent to the oldest => first column displays 2024-12-02 data, second column 2024-12-01, etc.

[lbendlin]

see attached.

[Poloscopie]

Hello Ibdendlin,

Unfortunately, the formula remains broken with the sorting by date descending.

We can see that the Qty Δ D-1 isn't showing the difference between D0 quantity and D-1 quantity.

As displayed in my previous screenshot, for category A, the delta should be -400 on 12/02, -200 on 12/01, etc. The values should be like in my message 7 (or the very first screenshot on the message 1).

[lbendlin · Accepted solution]

I missed the wrong PREVIOUSDAY reference

[Poloscopie]

And it's working 🙂

I think I get the logic, thanks a lot for your help on this topic @lbendlin!

[Sahir_Maharaj]

Hello @Poloscopie,

Can you please try this approach:

Qty Δ D-1 = 
VAR CurrentDate = MAX('Evolution of Qty'[Photo])
VAR PreviousDate =
    CALCULATE(
        MAX('Evolution of Qty'[Photo]),
        'Evolution of Qty'[Photo] < CurrentDate
    )
VAR PreviousQty =
    CALCULATE(
        SUM('Evolution of Qty'[Qty]),
        'Evolution of Qty'[Photo] = PreviousDate
    )
RETURN
    IF(
        ISBLANK(PreviousDate),
        BLANK(),
        SUM('Evolution of Qty'[Qty]) - PreviousQty
    )

[Poloscopie]

Unfortunately, this gives the same result.

I named your function "Qty Δ Test".

When dates are sorted the ascending way, it's ok:

When sorted the other way, your function returns blank: