Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

DAX Query: Relative difference visible previous day

Hi   I have the following query table yet (i'm not interested in a Table or Matrix visual):     How can i get a new column with the relative difference of one of the visible day with the p...
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi Anonymous 

    You can use the OFFSET function.

    Here is an example:

    Test =
VAR SummarizedTable =
    SUMMARIZE (
        'Table Principale',
        [Date Commande],
        "Somme Cash-Flow", SUM ( 'Table Principale'[Prix total avec rabais] )
    )
VAR AddPrevious =
    ADDCOLUMNS (
        SummarizedTable,
        "Somme Cash-Flow Previous",
            -- Guaranteed to be a single row
            SELECTCOLUMNS (
                OFFSET ( -1, SummarizedTable, ORDERBY ( 'Table Principale'[Date Commande] ) ),
                [Somme Cash-Flow]
            )
    )
RETURN
    AddPrevious

    I would also recommend changing SUMMARIZE (...) to ADDCOLUMNS ( SUMMARIZE (... ), ... ), but that's a best practice suggestion rather than a necessity here.

     

    Regards

OwenAuger's avatar
OwenAuger
Super User
2 years ago

Hi Anonymous 

You can use the OFFSET function.

Here is an example:

Test =
VAR SummarizedTable =
    SUMMARIZE (
        'Table Principale',
        [Date Commande],
        "Somme Cash-Flow", SUM ( 'Table Principale'[Prix total avec rabais] )
    )
VAR AddPrevious =
    ADDCOLUMNS (
        SummarizedTable,
        "Somme Cash-Flow Previous",
            -- Guaranteed to be a single row
            SELECTCOLUMNS (
                OFFSET ( -1, SummarizedTable, ORDERBY ( 'Table Principale'[Date Commande] ) ),
                [Somme Cash-Flow]
            )
    )
RETURN
    AddPrevious

I would also recommend changing SUMMARIZE (...) to ADDCOLUMNS ( SUMMARIZE (... ), ... ), but that's a best practice suggestion rather than a necessity here.

 

Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks a lot! That works like a charm.