Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

DAX - Subtract previous value with condition

  • 1.  DAX - Subtract previous value with condition

    Posted Feb 13, 2020 04:11 AM
    Hello,

    In the attached table, i tried to subtract the previous measurement to the n measurement (n - n-1) in the index1 column IF the device_id is the same.

    Do you know how to proceed?



    Thanks
    Matthieu CORRE

    ------------------------------
    Matthieu CORRE
    Energy manager
    ------------------------------


  • 2.  RE: DAX - Subtract previous value with condition

    Top Contributor
    Posted Feb 13, 2020 09:35 AM
    Hi Matthieu,

    Interesting topic...

    The trick is to add an auto incremental ID column with power query (add column tab & index column button)
    When done, we can play with earlier/earliest statements

    I put 2 different results depending on how you want the result to come out (see the picture attached)

    Column 1 =
    Datas[index1]
        - MAXX (
            FILTER (
                Datas,
                Datas[ID]
                    = EARLIER ( Datas[ID] ) - 1
                    && Datas[device_id] = EARLIEST ( Datas[device_id] )
            ),
            Datas[index1]
        )


    Column 2 =
    VAR PreviousRecords =
        FILTER (
            Datas,
            Datas[ID]
                = EARLIER ( Datas[ID] ) - 1
                && Datas[device_id] = EARLIEST ( Datas[device_id] )
        )
    RETURN
        IF (
            COUNTROWS ( PreviousRecords ) > 0,
            Datas[index1] - MAXX ( PreviousRecords, Datas[index1] )
        )​


    Hope this helps... Improvements and feedback welcome.. ;)


    ------------------------------
    Nicolas MENDEZ
    Data & BI consultant
    Becom Consulting
    ------------------------------



  • 3.  RE: DAX - Subtract previous value with condition

    Top Contributor
    Posted Feb 14, 2020 07:24 AM
    EARLIER() and EARLIEST() seem to become deprecated - you are supposed to replace them with inline variables in your DAX so you can better control the context where you grab the values.  Also, keep in mind that you can use TREATAS() in the same table, to great effect, as "The Italians"  have shown in a recent article.

    ------------------------------
    Lutz
    ------------------------------



  • 4.  RE: DAX - Subtract previous value with condition

    Posted Feb 14, 2020 08:52 AM
    ​Hi Nicolas,

    Thank you for your answer!
    I get some values with the second solution, but very few as you can see in the picture.
    And the fisrt one, only recovers index1.



    Do

    ------------------------------
    matthieu CORRE
    Energy manager
    ------------------------------



  • 5.  RE: DAX - Subtract previous value with condition

    Top Contributor
    Posted Feb 14, 2020 09:00 AM
    Matthieu,

    Cela vient du fait que ID n'est pas continu 1,2,3,4,5,6,7,.....

    ------------------------------
    Nicolas MENDEZ
    Data & BI consultant
    Becom Consulting
    ------------------------------



  • 6.  RE: DAX - Subtract previous value with condition

    Posted Feb 14, 2020 09:26 AM
    Nicolas,

    A priori, la fonction filtre ne suffit pas (cf. photo). Avez vous une autre solution?


    Matthieu

    ------------------------------
    matthieu CORRE
    Energy manager
    ------------------------------



  • 7.  RE: DAX - Subtract previous value with condition

    Posted Feb 14, 2020 09:47 AM
    ​Ok, it s work now.

    Thank you!

    Matthieu

    ------------------------------
    matthieu CORRE
    Energy manager
    ------------------------------



  • 8.  RE: DAX - Subtract previous value with condition

    Posted 12 days ago
    Edited by Martin Almerud 12 days ago
    Hi Nicolas

    I have datasets with mutliple devices and I need to do the same as over (subtract previous value from value) but the index will not work since it does not correlate with the devices.
    Do you know if this is possible to solve at all this way?


    ------------------------------
    Martin Almerud
    Technical MAnager
    ------------------------------