Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SaCvP125
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
BILASolution
Solution Specialist
Solution Specialist

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
BILASolution
Solution Specialist
Solution Specialist

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

@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

 

Hi @NatashaSchuster

 

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

 

explain.png

Regards

BILASolution

 

 

Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

CalculatedColumn with previous row.jpg


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.