Forum Discussion
Double condition - Stock table
hi, Baye
Just try this formula:
Column 2 = VAR MAXdt = CALCULATE ( MAX ( Stock[Datetime] ), FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) ) ) VAR MAXNumID = CALCULATE ( MAX ( Stock[Number] ), FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) && Stock[Datetime] = MAXdt ) ) RETURN CALCULATE ( SUM ( Stock[Stock] ), FILTER ( Stock, Stock[Number] = MAXNumID && Stock[Datetime] = MAXdt ) )or
Column 3 = VAR MAXdt = CALCULATE ( MAX ( Stock[Datetime] ), FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) ) ) VAR MAXNumID = CALCULATE ( MAX ( Stock[Number] ), FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) && Stock[Datetime] = MAXdt ) ) RETURN IF ( Stock[Datetime] = MAXdt && Stock[Number] = MAXNumID, CALCULATE ( SUM ( Stock[Stock] ) ) )Result:
Best Regards,
Linhi, Baye
Just try this measure
Measure = VAR MAXdt = CALCULATE ( MAX ( Stock[Datetime] ), ALLEXCEPT(Stock,Stock[IdProduct],'Calendar'[Month],'Calendar'[Month Number])) VAR MAXNumID = CALCULATE ( MAX ( Stock[Number] ), FILTER ( ALLEXCEPT(Stock,Stock[IdProduct]), Stock[Datetime] = MAXdt ) ) return CALCULATE ( SUM ( Stock[Stock] ), FILTER ( Stock, Stock[Number] = MAXNumID && Stock[Datetime] = MAXdt ) )Best Regards,
Lin
Community Supporthi, Baye
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Best Regards,
Lin
Hi,
Here's the excel workbook, it's a sample model regarding the two different cases I found:
Thank you very much!
B.
- v-lili6-msft
hi, Baye
Just try this formula:
Column 2 = VAR MAXdt = CALCULATE ( MAX ( Stock[Datetime] ), FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) ) ) VAR MAXNumID = CALCULATE ( MAX ( Stock[Number] ), FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) && Stock[Datetime] = MAXdt ) ) RETURN CALCULATE ( SUM ( Stock[Stock] ), FILTER ( Stock, Stock[Number] = MAXNumID && Stock[Datetime] = MAXdt ) )or
Column 3 = VAR MAXdt = CALCULATE ( MAX ( Stock[Datetime] ), FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) ) ) VAR MAXNumID = CALCULATE ( MAX ( Stock[Number] ), FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) && Stock[Datetime] = MAXdt ) ) RETURN IF ( Stock[Datetime] = MAXdt && Stock[Number] = MAXNumID, CALCULATE ( SUM ( Stock[Stock] ) ) )Result:
Best Regards,
LinHi! I'm back again...
I've copied your two options into two columns in my model and after I've created a measure for each one as MAX(Column2) (as the formula returns a value for each row), and SUM(Column3) (as the formula returns a value just for the last value).
I've noticed that your columns return the last value in the stock, but if I try to filter the values by month, your Column 2 returns the max of total stock (not filtered by month), and your Column 3 returns "blank" if the last value in stock is in another month from the filtered one.
Any of them return the correct stock for the last date/number in the selected filter.
Any idea of how to solve this issue?
Thanx!!!
B.
- v-lili6-msft
hi, Baye
First, you should know that calculated column and calculate table can't be affected by any slicer.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/Second, you could use “all” functions to add a measure
https://docs.microsoft.com/en-us/dax/allexcept-function-dax
Measure = VAR MAXdt = CALCULATE ( MAX ( Stock[Datetime] ), ALLEXCEPT(Stock,Stock[IdProduct])) VAR MAXNumID = CALCULATE ( MAX ( Stock[Number] ), FILTER ( ALLEXCEPT(Stock,Stock[IdProduct]), Stock[Datetime] = MAXdt ) ) return CALCULATE ( SUM ( Stock[Stock] ), FILTER ( Stock, Stock[Number] = MAXNumID && Stock[Datetime] = MAXdt ) )By the way: add month column into red part above.
Best Regards,
Lin