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
ale_BI
Helper I
Helper I

Help with DAX formula to calculate value from previous day

I need to create a calculated column 'Inventory_value' that calculates for each PV/product/date the inventory as follows:

The inventory of the day before for that PV/product + qty_em + qty_ref- qty_ven

If the inventory of the day before is not present, it becomes 0.

 

So far I've only made this and it doesn't work, because I always get a big value like as if its summing up all the rows together

inventory_value = 
var current_dt = rimanenze_pv_day_nonoil[dt]
var product = rimanenze_pv_day_nonoil[cost_prod]
var inventory_before =
    CALCULATE(
        SUM(rimanenze_pv_day_nonoil[em_total_value]),
        FILTER(
        rimanenze_pv_day_nonoil,
        rimanenze_pv_day_nonoil[dt] = current_dt - 1
        )
    )

var inventory_true = inventory_before + rimanenze_pv_day_nonoil[em_qty] + rimanenze_pv_day_nonoil[rett_qty] - rimanenze_pv_day_nonoil[ven_qty]

var result = 
    IF(
        ISBLANK(inventory_before),
        0,
        inventory_true
    )
return

result

The idea is that it returns that sum for each row, not all summed together. Does anyone have a clue on how to fix this? All the values are within the same table.

1 ACCEPTED SOLUTION

Thank you, I used the additional detail as an AND measure like so

    CALCULATE(
        SUM(rimanenze_pv_day_nonoil[em_total_value]),
        FILTER(
        rimanenze_pv_day_nonoil,
        rimanenze_pv_day_nonoil[dt] = current_dt - 1 && rimanenze_pv_day_nonoil[cost_prod]=product
        )
    )
 
I am not sure if this works, I will need to check thoroughly, but it does indeed give me more plausible results. 

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

you did not define the product?

inventory_value = 
var current_dt = rimanenze_pv_day_nonoil[dt]
var product = rimanenze_pv_day_nonoil[cost_prod]
var inventory_before =
    CALCULATE(
        SUM(rimanenze_pv_day_nonoil[em_total_value]),
        FILTER(
        rimanenze_pv_day_nonoil,
rimanenze_pv_day_nonoil[cost_prod]=product, rimanenze_pv_day_nonoil[dt] = current_dt - 1 ) ) var inventory_true = inventory_before + rimanenze_pv_day_nonoil[em_qty] + rimanenze_pv_day_nonoil[rett_qty] - rimanenze_pv_day_nonoil[ven_qty] var result = IF( ISBLANK(inventory_before), 0, inventory_true ) return result

Thank you, I used the additional detail as an AND measure like so

    CALCULATE(
        SUM(rimanenze_pv_day_nonoil[em_total_value]),
        FILTER(
        rimanenze_pv_day_nonoil,
        rimanenze_pv_day_nonoil[dt] = current_dt - 1 && rimanenze_pv_day_nonoil[cost_prod]=product
        )
    )
 
I am not sure if this works, I will need to check thoroughly, but it does indeed give me more plausible results. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.