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
draj
Frequent Visitor

Using FIFO in calculation

Hi All!

 

I have a unique issue that I would like to solve and would definitely appreciate any help. I have a sample data such as below:

 

Avg Cost Calculation 1

Screenshot 2020-05-28 at 9.03.19 AM.png

 

Avg Cost Calculation 2

Screenshot 2020-05-28 at 9.03.28 AM.png

 

Here I have 2 examples of calculation average cost, however I need to achieve Average Cost Calculation 2.

 

In this example, Total Cost is Unit_Cost_Price * Quantity + Fee (which is the right most column)

 

The easy way of calculation the average cost would be to sum up the total cost and divide it by the sum of quantity (which is calculation 1)

 

However I need to achieve the calculation using FIFO, where the first 2 items are ignored (the items are sorted by date from oldest to newest) as the quantity cancels each other, and the average cost is calculated after.

 

Would definitely appreciate any suggestions on achieving this in powerbi

1 ACCEPTED SOLUTION

Hi, @draj 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

a1.png

 

Youm may create calculated columns and measures as below.

Calculated column:
Running Qty = 
var _date = 'Table'[Date]
var _lastdate =
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        ALL('Table'),
        'Table'[Date]<_date&&
        'Table'[Quantity]<0
        
    )
)
var _startdate = 
CALCULATE(
    MIN('Table'[Date]),
    FILTER(
        ALL('Table'),
        'Table'[Date]>_lastdate
    )
)
return
IF(
    ISBLANK(_lastdate),
    CALCULATE(
        SUM('Table'[Quantity]),
        FILTER(
            ALL('Table'),
            'Table'[Date]<=EARLIER('Table'[Date])
        )
    ),
    CALCULATE(
        SUM('Table'[Quantity]),
        FILTER(
            ALL('Table'),
            'Table'[Date]>=_startdate&&
            'Table'[Date]<=EARLIER('Table'[Date])
        )
    )
) 
Running Cost = 
var _date = 'Table'[Date]
var _lastdate =
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        ALL('Table'),
        'Table'[Date]<_date&&
        'Table'[Quantity]<0
        
    )
)
var _startdate = 
CALCULATE(
    MIN('Table'[Date]),
    FILTER(
        ALL('Table'),
        'Table'[Date]>_lastdate
    )
)
return
IF(
    ISBLANK(_lastdate),
    CALCULATE(
        SUM('Table'[Total Cost]),
        FILTER(
            ALL('Table'),
            'Table'[Date]<=EARLIER('Table'[Date])
        )
    ),
    CALCULATE(
        SUM('Table'[Total Cost]),
        FILTER(
            ALL('Table'),
            'Table'[Date]>=_startdate&&
            'Table'[Date]<=EARLIER('Table'[Date])
        )
    )
) 

Measure:
avg 1 = SUM('Table'[Total Cost])/SUM('Table'[Quantity])
avg 2 = 
var _maxdate = 
CALCULATE(
    MAX('Table'[Date]),
    ALL('Table')
)
return
DIVIDE(
    LOOKUPVALUE('Table'[Running Cost],'Table'[Date],_maxdate),
    LOOKUPVALUE('Table'[Running Qty],'Table'[Date],_maxdate)
)

 

Result:

a2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

@draj - You want Lookup Min/Max https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

 

You will "lookup" your max date and then use that to get your other two values.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler I am not looking based on the max date. Lets assume that there are 2 more after the third entry, and the quantity are all positive. The goal is to then calculate the average based on these 3 entrys (1 current + 2 new entries)

 

 

@draj - That certainly didn't come across in the original post and I am still not sure I am clear on the requirement. So, are you saying that you want any entries after the last negative entry? Still seems like Lookup Min/Max to me if that is the case. Lookup the MAXX date of all negative values. Use that date in your filter to filter for rows after that date. Then you can do the appropriate SUMX, etc. on those FILTER'ed rows.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  Thanks for your feedback. Let me go in more detail.

 

I will introduce a column call running QTY, which would make more sense as it explains the concept of FIFO

Screenshot 2020-05-28 at 10.18.21 AM.png

 

Lets take a look at this 3 entries, which have been sorted by date (old to new). We have a running quantity  column. At this point of time, the correct way of calculating average cost would be to ignore the first 2 items (as the quantity cancels each other), and just consider the last entry.

 

Now lets looks at a scenario where another entry is added, increasing the quantity:

 

Screenshot 2020-05-28 at 10.18.32 AM.png

 

SO here we have item 4, and the running quantity is now 2,000,000. The average cost is now calculated as (Total Cost of 3 and 4)/(Last Running Quantity).

 

You can see that it is more dynamic than the usual average calculation (SUM of Total Cost / SUM of Quantity)

Hi, @draj 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

a1.png

 

Youm may create calculated columns and measures as below.

Calculated column:
Running Qty = 
var _date = 'Table'[Date]
var _lastdate =
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        ALL('Table'),
        'Table'[Date]<_date&&
        'Table'[Quantity]<0
        
    )
)
var _startdate = 
CALCULATE(
    MIN('Table'[Date]),
    FILTER(
        ALL('Table'),
        'Table'[Date]>_lastdate
    )
)
return
IF(
    ISBLANK(_lastdate),
    CALCULATE(
        SUM('Table'[Quantity]),
        FILTER(
            ALL('Table'),
            'Table'[Date]<=EARLIER('Table'[Date])
        )
    ),
    CALCULATE(
        SUM('Table'[Quantity]),
        FILTER(
            ALL('Table'),
            'Table'[Date]>=_startdate&&
            'Table'[Date]<=EARLIER('Table'[Date])
        )
    )
) 
Running Cost = 
var _date = 'Table'[Date]
var _lastdate =
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        ALL('Table'),
        'Table'[Date]<_date&&
        'Table'[Quantity]<0
        
    )
)
var _startdate = 
CALCULATE(
    MIN('Table'[Date]),
    FILTER(
        ALL('Table'),
        'Table'[Date]>_lastdate
    )
)
return
IF(
    ISBLANK(_lastdate),
    CALCULATE(
        SUM('Table'[Total Cost]),
        FILTER(
            ALL('Table'),
            'Table'[Date]<=EARLIER('Table'[Date])
        )
    ),
    CALCULATE(
        SUM('Table'[Total Cost]),
        FILTER(
            ALL('Table'),
            'Table'[Date]>=_startdate&&
            'Table'[Date]<=EARLIER('Table'[Date])
        )
    )
) 

Measure:
avg 1 = SUM('Table'[Total Cost])/SUM('Table'[Quantity])
avg 2 = 
var _maxdate = 
CALCULATE(
    MAX('Table'[Date]),
    ALL('Table')
)
return
DIVIDE(
    LOOKUPVALUE('Table'[Running Cost],'Table'[Date],_maxdate),
    LOOKUPVALUE('Table'[Running Qty],'Table'[Date],_maxdate)
)

 

Result:

a2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-alq-msft Thank you so much!!

@draj - Then why not just use the Running Total Quick Measure that is built into Power BI to create that column? And probably another running total column for Total Cost. Then it truly is a Lookup Min/Max situation as your last date will have the totals you want.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.