topic Re: get value from the previous month in DAX Commands and Tips

get value from the previous month

vvenditt — Wed, 06 Oct 2021 13:27:47 GMT

hello, hope you all are well!

I have a problem in a DAX measure.
I need to capture the previous result.
normally I would use DATEADD or PREVIOUSMONTH for this, but my problem is:
in my example, I don't have data for month 3, so in the analysis for month 4 I need to capture the last one before it with value
in this case month 2

here a small sample of the data:

Can someone help me? Thank you very much!

Re: get value from the previous month

Greg_Deckler — Wed, 06 Oct 2021 13:37:21 GMT

vvenditt You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Re: get value from the previous month

vvenditt — Wed, 06 Oct 2021 15:35:39 GMT

Thank you very much for your help. the material will help me as a reference, but I still haven't been able to solve my problem.

the main point here is this:
if I'm evaluating the line of period 4, I want to compare it with period 3, but if there is no value in period 3, I want to compare it to period 2.

in my case I do not need to accumulate, but rather pay the amount prior to the period of the valuation context

Re: get value from the previous month

vvenditt — Wed, 13 Oct 2021 11:36:08 GMT

hello I managed to solve it with this DAX code:

previous result =
var period_context = MAX (data [period])

var previous_period =
CALCULATE(
   MAX (data [period]),
   FILTER(
      ALL (data [period]),
      data [period] <period_context &&
      SUM (data [value]) <> BLANK ()
   )
)

var previous_month_value =
CALCULATE(
   SUM (data [value]),
   FILTER(
      ALL (data [period]),
      data [period] = previous_period
   )
)

Return
if(
   ISINSCOPE (data [period]),
   previous_month_value
)

Re: get value from the previous month

vvenditt — Wed, 13 Oct 2021 11:36:27 GMT

tks

Re: get value from the previous month

Anonymous — Tue, 18 Jul 2023 13:24:55 GMT

Hello I tried this DAX and its very useful but may I ask how about if there's an order that needs to fill in like the image below. I need ($1535) to be in Mar 2022 since its the next month not in Apr 2022.

2nd column is the current month and 3rd column is the previous month.
Thanks in advance.