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.

  • Theo_Alex's avatar
    Theo_Alex
    Regular Visitor
    2 years ago

    Hi!
    thanks for taking your precious time to help me out, I really appreciate it. I implented as you mentioned but I guess I described it wrongly since I don't get the desired values I am expecting. I attached a picture for a better understanding.

     

    I pre filtered all data for User_ID = 5. So we need to keep in mind that here a several more User_IDs in this table. FK stands for Foreign Key.

     

    Now I have several Entries (4 in Total: Entry_ID [5,6,8,10]). Entry with Entry_ID = 10 (May 14th 2024) is the newest / Last Entrey. Entry with Entry_ID = 5 (April 23rd 2024) is the oldest / first entry this userhas made. So for the user's first Entry there can't be any difference since it is its first Entry the is no score before. 

     

    Now I want to calculate the difference for each current Entry and its score. For instance for Score_ID = IP for the Current Entry_ID = 10 would be 9 - 3 since the entry before was on May 9th with Entry_ID = 8. For Score_ID = MS would be 3 - 3 = 0 and so on.

     

    The Difference of Score_ID = IP for the Entry_ID = 8 (May 9th 2024) would be the difference between score from Entry_ID = 8 (May 9th 2024) and Entry_ID = 5 (April 24th 2024) and so on. In this Case 3-3 = 0.

     

    So we always have a current Score we are looking at and want to deduct the Entry before for each Score_ID and User_ID.

     

    I hope I was able to express myself better now.

     

    Thanks again in advance!

     

    • xifeng_L's avatar
      xifeng_L
      Super User
      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~

       

      • Theo_Alex's avatar
        Theo_Alex
        Regular Visitor
        2 years ago

        It worked out! I Had to change the format of the date in the database before I load it into my model but since I changed the format so PBI could recognize it as date I could calculate the differences between all entries per User, per Score, per Entry. And the first Entry has of course no data. Nice Thanks!!