topic Re: calculation using Previous value empty in DAX Commands and Tips

calculation using Previous value empty

ingpiolo — Thu, 31 Aug 2023 07:22:01 GMT

Hi everyone,

I have a table like this:

I should calculate subsequent fields as indicated iteratively by associating a measure (Filled fields are final values while empty ones are an estimate of future values based on the selection of a parameter).

Do you have any suggestions?

Many thanks

Re: calculation using Previous value empty

JoeBarry — Thu, 31 Aug 2023 08:47:34 GMT

Hi ingpiolo

To get the previous months data try this

Previous Month = CALCULATE(SUM('Table'[Valore]), DATEADD('Table'[Date], -1, MONTH))

I would think then that you should create a new measure

[Previous Month] + [YourMeasure]

Thanks

Joe

If this post helps, then please Accept it as the solution

Re: calculation using Previous value empty

ingpiolo — Thu, 31 Aug 2023 08:59:47 GMT

Thanks Joe for the reply.
The problem is that I have empty cells and therefore with the measure
Previous Month = CALCULATE(SUM('Table'[Value]), DATEADD('Table'[Date], -1, MONTH))

I get correctly only the next first value referring to 01/07/2023.
Subsequent ones should resume the value at 01/07/2023 which is saved as a measure. This is the result I would like to get

Re: calculation using Previous value empty

Anonymous — Tue, 05 Sep 2023 09:36:08 GMT

Hi ingpiolo ,

I created a sample pbix file(see the attachment), please find the details in it.

Result = VAR _seldate = SELECTEDVALUE ( 'Table'[Date] ) VAR _maxdate = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( ALLSELECTED ( 'Table' ), [Sum of value] > 0 ) ) VAR _value = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _maxdate ), [Sum of value] ) VAR _measures = CALCULATE ( SUM ( 'Table'[Measure] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= _seldate ) ) RETURN IF ( ISBLANK ( SELECTEDVALUE ( 'Table'[Measure] ) ), BLANK (), _value + _measures )

Best Regards

Re: calculation using Previous value empty

ingpiolo — Thu, 07 Sep 2023 07:13:35 GMT

thanks a lot for the support.

I tried but unfortunately I didn't succeed. I am attaching the file to clarify my situation.

Thanks for your support

https://drive.google.com/file/d/15fXdGIKlAwNIQbEWUGQow9kBN0DCmZZm/view?usp=drive_link

Re: calculation using Previous value empty

Anonymous — Thu, 07 Sep 2023 09:23:25 GMT

Hi ingpiolo ,

It seems that I have no access to your shared file, could you please grant me the proper permisson to it? Thank you.

Best Regards

Re: calculation using Previous value empty

ingpiolo — Thu, 07 Sep 2023 11:30:13 GMT

Ok, I should have unlocked access. you can check?

Thank you