Dax to sum value grouped by two columns

[amalsperera]

Hi,

I have the following stock table

Date	Material	Batch	Stockvalue
9/13/2023	A	2	100
9/12/2023	A	2	150
9/10/2023	B	1	75
9/9/2023	A	2	200
9/5/2023	A	1	5

A stock record will only be written if there is a transaction for that stock.

I need to get the total value of the stock. 

Eg. if I check the stock value for 9/13/2023 for Material A the value should by 105 as Batch number 1 will have a value 5 and batch number 2 will have a value 100. 

How do I get this done using Dax. 

Thanks in advnce

Amal

[barritown]

Hi @amalsperera,

Here's an option how to solve your problem:

And in plain text for convenience:

Measure = 
VAR _maxDate = MAX ( [Date] )
VAR _tbl = 
    ADDCOLUMNS ( 
        SUMMARIZE ( FILTER ( ALL ( Data ), [Date] <= _maxDate ), [Material], [Batch], "Latest Date", MAX ( [Date] ) ),
        "Stockvalue",
        VAR _currentMaterial = [Material]
        VAR _currentBatch = [Batch]
        VAR _currentDate = [Latest Date]
        RETURN MINX ( FILTER ( ALL ( Data ), [Material] = _currentMaterial && [Batch] = _currentBatch && [Date] = _currentDate ), [Stockvalue] ) )
RETURN SUMX ( _tbl, [Stockvalue] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[amalsperera]

Hi @barritown ,

Could you please attach the pbix. I am getting the total stock value as the value for all items. 

Thanks 

Amal

[barritown]

Sure, here it is 

https://www.dropbox.com/scl/fi/5t7xyxzujap3qh86ripgq/community-stock.pbix?rlkey=0cg0kaicaalc2kr5wkpl...

I think I forgot to mention an additional dimension table I used - you'll see in the file.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[amalsperera]

Hi @barritown ,

I tried it out, but it still is not giving per SKU. Could you please check the attached pbix and see what I am doing wrong?

https://drive.google.com/file/d/17__K8x_EAdTUxeyinDHdw-WN5ggwbfxN/view?usp=drive_link

Thanks

Amal

[barritown]

Hi @amalsperera,

Unfortunately, I cannot access the file you tried to share. Could you please double check sharing settings?

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[amalsperera]

Hi @barritown,

Sorry I could not respond earlier. I was out of office. Could you please try now?

https://drive.google.com/file/d/17__K8x_EAdTUxeyinDHdw-WN5ggwbfxN/view?usp=sharing

Amal

[barritown · Accepted solution]

Hi @amalsperera,

I've updated it a little bit. Please check if it works correctly - https://www.dropbox.com/scl/fi/d45372koa4xjr3qfylqo7/Sample.pbix?rlkey=nac40bon3nggko254pg36toee&dl=....

I assume you don't need Total. If you do, another update will be needed.

[amalsperera]

Hi @barritown ,

Thanks a bunch. I fiddled around with the measure and got the totals as well. This is the dax for it.

M_Distributor_Stock =
VAR _maxDate = MAX ( DistributorInventoryValue[Date] )
VAR _tbl =
ADDCOLUMNS (
        SUMMARIZE ( FILTER(ALLEXCEPT(DistributorInventoryValue
                                             ,DistributorInventoryValue[MaterialNo])
                                             , DistributorInventoryValue[Date] <= _maxDate )
                                 , [MaterialNo]
                                 , [BatchSerialNumber]
                                 , "Latest Date", MAX ( DistributorInventoryValue[Date] ) ), "max Date" , _maxDate

                                 , "Stock_value",
                                          VAR _currentMaterial = [MaterialNo]
                                          VAR _currentBatch = [BatchSerialNumber]
                                          VAR _currentDate = [Latest Date]

                                          Return CALCULATE(MINX(FILTER(DistributorInventoryValue
                                                        , [MaterialNo] = _currentMaterial
                                                         && [BatchSerialNumber] = _currentBatch
                                                         && [Date] = _currentDate)
                                                         ,[StockValue])
                                                    ,ALL('Date')))
RETURN SUMX ( _tbl, [Stock_value] )

I have accepted your post as the solution as you pointed me in the right direction. I have been struggling with this for some time. 

Thanks again and Cheers,

Amal