cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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_ConsumedYearTickets_LostedYear
10201732017
252016352016
82015312015
12014122014

 

And I'm trying to calculate the following formula in PowerBI:

Tickets_ConsumedYearTickets_LostedYear Result
10201732017 39 (32+10-3)
252016352016 

29

(39+25-35)

82015312015 

6

(29+8-31)

12014122014 

-5

(6+1-12)

 

How do I get the result:

Current_Number of Tickets Consumed = 32
Number of Tickets Consumed;

Number of Tickets Losted;

 

How can I do this using PowerBI? I can share the excel to see what I'm calculating

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Specialist
Solution Specialist

Re: Calculations with previous row

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...

 

reporte.png

 

 

I hope this helps

 

Regards

BILASolution

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Re: Calculations with previous row

Hi @SaCvP125

 

This calculated column shall get you desired result hopefully.

Modify Table Name

Result=
32
    + CALCULATE (
        SUM ( Table1[Tickets_Consumed] ),
        FILTER ( Table1, Table1[Year] >= EARLIER ( Table1[Year] ) )
    )
    - CALCULATE (
        SUM ( Table1[Tickets_Losted] ),
        FILTER ( Table1, Table1[Year] >= EARLIER ( Table1[Year] ) )
    )
Try my new Power BI game Cross the River
Highlighted
Super User III
Super User III

Re: Calculations with previous row

CalculatedColumn with previous row.jpg

Try my new Power BI game Cross the River
Highlighted
Solution Specialist
Solution Specialist

Re: Calculations with previous row

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...

 

reporte.png

 

 

I hope this helps

 

Regards

BILASolution

View solution in original post

Highlighted
Helper II
Helper II

Re: Calculations with previous row

@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

 

Highlighted
Solution Specialist
Solution Specialist

Re: Calculations with previous row

Hi @NatashaSchuster

 

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

 

explain.png

Regards

BILASolution

 

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors