cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SaCvP125 Regular Visitor
Regular 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
BILASolution Established Member
Established Member

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

5 REPLIES 5
Super User
Super User

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] ) )
    )
Super User
Super User

Re: Calculations with previous row

CalculatedColumn with previous row.jpg

BILASolution Established Member
Established Member

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

NatashaSchuster Regular Visitor
Regular Visitor

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

 

BILASolution Established Member
Established Member

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
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 303 members 2,992 guests
Please welcome our newest community members: