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
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
Employee
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
Employee
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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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