Forum Discussion

SaCvP125's avatar
SaCvP125
Frequent Visitor
8 years ago
Solved

Calculations with previous row

Hi guys,   I've the following dataset that shows me the numbe of consumed and losted tickets in last 4 years. And I know that I have 32 tickets consumed for the next year. Tickets_Consumed Yea...
Need Help
Tips and Tricks
  • BILASolution's avatar
    BILASolution
    8 years ago

    Hi SaCvP125

     

    Try this measures...

     

    Total Consumed = SUM(Table1[Tickets Consumed]) 
    Total Losted = SUM(Table1[Tickets Losted]) 
    Current Number = 32 
    Result = var actualyear = FIRSTNONBLANK(Table1[Year];1) return 

           CALCULATE([Current Number] + [Total Consumed] - [Total Losted];ALL(Table1);Table1[Year]>=actualyear)

    and this is the final result...

     

     

     

    I hope this helps

     

    Regards

    BILASolution

BILASolution's avatar
BILASolution
Solution Specialist
8 years ago

Hi SaCvP125

 

Try this measures...

 

Total Consumed = SUM(Table1[Tickets Consumed]) 
Total Losted = SUM(Table1[Tickets Losted]) 
Current Number = 32 
Result = var actualyear = FIRSTNONBLANK(Table1[Year];1) return 

           CALCULATE([Current Number] + [Total Consumed] - [Total Losted];ALL(Table1);Table1[Year]>=actualyear)

and this is the final result...

 

 

 

I hope this helps

 

Regards

BILASolution

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    BILASolution

     

    Your solution is pretty slick. It looks simple but it's surprisingly little hard to undersatnd fully. Do you mind being so kind to explain how your code works: 

     

     

     

     

    Result =
VAR actualyear = FIRSTNONBLANK ( Table1[Year]; 1 )
RETURN
    CALCULATE (
        [Current Number] + [Total Consumed] - [Total Losted];  
        ALL ( Table1 );
        Table1[Year] >= actualyear
    )

     

    Thanks

     

    • BILASolution's avatar
      BILASolution
      Solution Specialist
      8 years ago

      Hi Anonymous

       

      The next picture is a summary of the logic that I used...I hope you could understand

       

      Regards

      BILASolution