Forum Discussion

Theo_Alex's avatar
Theo_Alex
Regular Visitor
2 years ago
Solved

Difference between two entries

Hi ya’ll,   I have a problem regarding my data model and its calculation. I have a data model which is organized in rows. Quite similar to an OLAP/Cube.   Each User and Entry gets an ID. Also eac...
"CALCULATE"
"DAX"
  • xifeng_L's avatar
    xifeng_L
    2 years ago

    Hi Theo_Alex ,

     

    You can try below measure.

     

    Last Entry = 
CALCULATE(
    SUM('Table'[Score]),
    OFFSET(
        -1,
        ALL('Table'[User_ID],'Table'[Score_ID],'Table'[Entry_ID],'Table'[Entry_Date]),
        ORDERBY('Table'[Entry_Date],ASC),
        PARTITIONBY('Table'[User_ID],'Table'[Score_ID])
    )
)

     

    Demo - 5-31calculate Difference between two entries.pbix

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Theo_Alex ,

First of all, many thanks to gmsamborn  for your very quick and effective replies.

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to calculate score for last entry.

Last Entry = 
var entr_i = MAX('Table'[Entry_ID])
var _Max = CALCULATE(MAX('Table'[User_ID]), FILTER('Table', 'Table'[Entry_ID] = entr_i))
Return
Calculate(SUM('Table'[Score]),filter(ALLEXCEPT('Table', 'Table'[Score_ID]), 'Table'[User_ID] = _Max  && 'Table'[Entry_ID] > entr_i ))

3.Create the new measure to the difference between two entries.

Difference two entres = IF(ISBLANK([Last Entry]), BLANK(), [Last Entry] - SUMX('Table', 'Table'[Score]))

4.Drag two measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.