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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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