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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.