none
dax find previous round value RRS feed

  • Question

  • hello...

    I have a fact


    id val round
    1 10 1
    2 2 1
    3 1 1
    4 20 11
    5 15 30
    6 15 30

    and I have a round Dim wich related to column round in the fact

    ID DESC order
    1 ROUND 1 1
    11 ROUND 2 2
    30 ROUND 3 3

    I need to find sum of values in round for previous round... 

    the output should be like

    Column1 sum of value Previous ROUND VALUE
    ROUND 1 13  
    ROUND 2 20 13
    ROUND 3 30 20
    • Edited by radissoufan Sunday, October 13, 2019 5:53 PM
    Sunday, October 13, 2019 5:47 PM

All replies

  • Hi radissoufan,

    Thanks for your question.

    Per your description, you could follow the following steps to get expected results.

    Step 1 Create a calculated table, the DAX for the calculated table is like this.

    CalculatedTable 1:=SUMMARIZE(fact_table,fact_table[round],"sum of value",SUM(fact_table[val]))

    Step 2 Add calculated columns to that table by using LOOKUPVALUE function.

    Index:=LOOKUPVALUE(Dim_round[order],Dim_round[ID],'CalculatedTable 1'[round]) Column1:=LOOKUPVALUE(Dim_round[DESC],Dim_round[ID],'CalculatedTable 1'[round]) Previous ROUND VALUE:=LOOKUPVALUE('CalculatedTable 1'[sum of value],'CalculatedTable 1'[Index],'CalculatedTable 1'[Index]-1)

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 14, 2019 5:48 AM
  • Hi radissoufan,

    Have you got your answer?  Any feedback here?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 21, 2019 6:28 AM