topic Re: MAX value previous month of the same column in DAX Commands and Tips

MAX value previous month of the same column

KW123 — Mon, 08 Aug 2022 21:37:25 GMT

I have one column on an excel sheet that I need to put into my pbi dashboard.

It's called [Where we actually are YTD] and it is based on a calculation from [YTD funded] which will start at $0 for January 1st, 2022. To get each value for each day, it is added with a calculated column called WWAA.

Where I am having a hard time is in February onwards. To get the first value for February [YTD Funded] for the Where we actually are YTD, I need to take the MAX (or the last value of the previous month) of the Where we actually are YTD.

So if January 31st is $1000 for example, then Feb 1st will be MAX[where we actually are YTD] +[WWAA] = X

To summarize:
[YTD funded] = MAX/Last date of [Where we actually are YTD] (where January will start at $0)
[Where we actually are YTD] = [YTD funded] + [WWAA] <--- a calculated measure

Is something like this possible?

Re: MAX value previous month of the same column

KW123 — Mon, 08 Aug 2022 21:42:13 GMT

February example:
YTD Funded is the MAX of January (example, $1000)
WWAA rolling sum

Day	YTD funded	Where we actually are YTD [YTD funded] + [WWAA]
1	$1000	$1005
2	$1000	$1050
3	$1000	$1500
...28	$1000	$1800

Then in March, it'll be $1800 + [WWAA] For March 1st.

Re: MAX value previous month of the same column

Ethan96 — Thu, 11 Aug 2022 09:03:47 GMT

Hi， KW123

Not fully sure what the question.

How did you calculate the value of [WWAA]?

If the calculation involves iterative operations, it is recommended to pre-process in Excel before importing into PowerBI.

Regards,

Ethan

Re: MAX value previous month of the same column

KW123 — Thu, 11 Aug 2022 15:41:12 GMT

Hi Ethan96
Thanks for the reply.
The DAX for WWAR is

WWAR = CALCULATE(SUM('Loan (all)'[Original Balance]),

DATESMTD(('Dates'[Date]))) +0

I think where I am struggling is that my previous month doesn't seem to be returning the previous month. It returns a blank, or the current month.

Prevmonthc =

CALCULATE([CurrentMonth], ('Dates'[Month] -1 ))

If I try and just get a column of the previous month dates with

PREVIOUSMONTH('Dates'[Date])
The error message is 'A table of multiple values was supplied where a single value was expected'

Re: MAX value previous month of the same column

KW123 — Thu, 11 Aug 2022 17:25:45 GMT

To clarify,
I have a column for Last day of previous month,

Last day of previous month =

PREVIOUSMONTH('Dates'[Last day of month])

and I have a Dax for MAX value of the column I am trying to calculate

MAXWhatdoesYTDtotalneedtobe =

MAXX(

KEEPFILTERS(VALUES('Dates'[Date])),

CALCULATE([What does YTD total need to be])

)
But when create a Dax to show the Max of the PREVIOUS month, it is showing the current month in the report.

If I use this DAX

Prevmonthvalue =

CALCULATE([MAXWhatdoesYTDtotalneedtobe],[Last day of previous month])
Then it returns an error saying a Placeholder has been used in a true/false expression.....