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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Jonathas
Frequent Visitor

Help to calculate the stock amount

Dear, Good afternoon, i'm breaking my head with this problem, i need to calculate the balance stock with last amount  by product by inventory, my data model is:


Date Product Amount Inventory
02/01/2018 1,00 1119 801
08/01/2018 1,00 1120 801
09/01/2018 1,00 1110 801
10/01/2018 1,00 1111 801


I'de like that result stay like this with amount by day:


Date Product Amount Inventory
02/01/2018 1,00 1119 801
03/01/2018 1,00 1119 801
04/01/2018 1,00 1119 801
05/01/2018 1,00 1119 801
06/01/2018 1,00 1120 801
07/01/2018 1,00 1120 801
08/01/2018 1,00 1120 801
09/01/2018 1,00 1110 801
10/01/2018 1,00 1111 801


i made this formula but the result don't work with i'de, someone have any idea ?


amountCurrent =
VAR ultimoValor = CALCULATE(
    SUM('public f_saldo_historico'[amount]);
    FILTER(
        'public d_data';
        MAX('public f_saldo_historico'[date]) = 'public d_date'[date]
    )
)

 

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @Jonathas,

 

Please download the demo from the attachment. 

Measure =
VAR stopDate =
    CALCULATE (
        MAX ( 'public f_saldo_historico'[Date] ),
        ALL ( 'public d_date' ),
        ALL ( 'public f_saldo_historico' )
    )
VAR lastMaxDate =
    CALCULATE (
        MAX ( 'public f_saldo_historico'[Date] ),
        FILTER (
            ALL ( 'public d_date'[Date] ),
            'public d_date'[Date] <= MAX ( 'public d_date'[Date] )
        )
    )
RETURN
    IF (
        MAX ( 'public d_date'[Date] ) <= stopDate,
        CALCULATE (
            MAX ( 'public f_saldo_historico'[Amount] ),
            FILTER ( ALL ( 'public d_date'[Date] ), 'public d_date'[Date] = lastMaxDate )
        )
    )

Help-to-calculate-the-stock-amount

 

Best Regards,
Dale

Community Support Team _ Dale
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

4 REPLIES 4
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @Jonathas,

 

Please download the demo from the attachment. 

Measure =
VAR stopDate =
    CALCULATE (
        MAX ( 'public f_saldo_historico'[Date] ),
        ALL ( 'public d_date' ),
        ALL ( 'public f_saldo_historico' )
    )
VAR lastMaxDate =
    CALCULATE (
        MAX ( 'public f_saldo_historico'[Date] ),
        FILTER (
            ALL ( 'public d_date'[Date] ),
            'public d_date'[Date] <= MAX ( 'public d_date'[Date] )
        )
    )
RETURN
    IF (
        MAX ( 'public d_date'[Date] ) <= stopDate,
        CALCULATE (
            MAX ( 'public f_saldo_historico'[Amount] ),
            FILTER ( ALL ( 'public d_date'[Date] ), 'public d_date'[Date] = lastMaxDate )
        )
    )

Help-to-calculate-the-stock-amount

 

Best Regards,
Dale

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

@v-jiascu-msft I hava a new problem, when i select more than product, the total stay mistake, because it do not consider the total by product, can you help me
?

different resultdifferent result

Hi @Jonathas,

 

I can't see a total in your image. I just see two records (rows). I would suggest you create a new post in this forum with a sample and the formula you used. 

 

 

Best Regards,
Dale

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

Thank so much by attention, @v-jiascu-msft was solved.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.