Inventory measure

[MikaelDC]

Hi,

Hope somebody can help me. I need to create a measure that acumulates my inventory value. But all I can get is the sum of the movements within the specific period.

I've searched for solutions and trid the ones I could find. However they all calculate the same thing. They also calculate the sum of the movements within the specific period.

The ones I've trid so far.

Measure = CALCULATE(SUM(INVENTTRANS[COSTAMOUNTPOSTED]);FILTER(ALL(INVENTTRANS[DATEPHYSICAL]);INVENTTRANS[DATEPHYSICAL] <= MAX(INVENTTRANS[DATEPHYSICAL])))

Measure 2 = CALCULATE (SUM(INVENTTRANS[COSTAMOUNTPOSTED]);ALLSELECTED(INVENTTRANS[COSTAMOUNTPOSTED]);FILTER(ALLSELECTED(INVENTTRANS[DATEPHYSICAL]);ISONORAFTER(INVENTTRANS[DATEPHYSICAL];MAX(INVENTTRANS[DATEPHYSICAL]);DESC)))

Measure 3 = CALCULATE(SUMX(INVENTTRANS;INVENTTRANS[COSTAMOUNTPOSTED]);FILTER(INVENTTRANS;INVENTTRANS[DATEPHYSICAL].[Date] <= INVENTTRANS[DATEPHYSICAL].[Date]))

Best regards

Mikael

[TomMartens]

Hey Mikael,

I'm not totally sure what you are going to achieve, for this reason you may have a look at this pbix file and in this file at the report page "Accumulated Amount".

Please be aware that in this example a separate Calendar table (the one side) is used that is related to the fact table "FactWithDates" (the many side).

Within the above mentioned report I use this measure

Accumulated Amount = 
	CALCULATE(
		SUM('FactWithDates'[Amount])
		,FILTER(ALL('Calendar'[Date])
			,'Calendar'[Date] <= MAX('Calendar'[Date])
		)
	)

This measure aggregates all the values from prior periods of the measure Amount to current period:

Hope this helps

[MikaelDC]

Hi Tom,

Thank you very much for your reply. I think it's more or less the same measure as my first one. So I don't understand why it's not working.

What I'm trying to do is what you've done. Amount = costamountposted and date = datephysical. Could it be that my datephysical column isn't indexet?

I've tried to use your formula as well but with same result.

Measure 2 =
 CALCULATE(
  SUM(INVENTTRANS[COSTAMOUNTPOSTED])
  ;FILTER(ALL(INVENTTRANS[DATEPHYSICAL])
   ;INVENTTRANS[DATEPHYSICAL] <= MAX(INVENTTRANS[DATEPHYSICAL])
  )
 )

Really hoping that you can help me.

Best regards

Mikael

[v-ljerr-msft · Accepted solution]

Hi @MikaelDC,

What columns are you showing with the cumulative measure on your report?

Could you try using ALL ( INVENTTRANS ) instead of ALL ( INVENTTRANS[DATEPHYSICAL] ) in the FILTER of the formula to see if it works? 

Measure 2 =
CALCULATE (
    SUM ( INVENTTRANS[COSTAMOUNTPOSTED] );
    FILTER (
        ALL ( INVENTTRANS );
        INVENTTRANS[DATEPHYSICAL] <= MAX ( INVENTTRANS[DATEPHYSICAL] )
    )
)

Regards

[MikaelDC]

Hi v-ljerr-msft,

It's working. 🙂

Thank you very much for your help.

Best regards

Mikael

[TomMartens]

Hey,

besides that my measure uses a separate calendar table, it's the same.

The sample report in my pbix file also contains a 2nd measure that uses the date column from the fact table, your inventtrans table. With the expected results.

What I'm going to say, without  sample data that allows to recreate your issue, it's not possible (at least not for me) to provide further assistance.

Just one try, do you have a calendar table, that is related to the PHYSICALDATE column, and do you in your report a date column that comes from this calendar table?

Regards