Convert previous month expression to last month not working ?

[admin11]

Hi All

I got the previous month (Current Month -2)expression below working fine :-

Current Month -2 EXP = CALCULATE(GL[AMOUNT],eomonth('Date'[Date],0) = eomonth(today(),-1),GL[1_EXP] = "EXP")

I try to modify the above expression to get Last month amount :-

Current Month -1 EXP = CALCULATE(GL[AMOUNT],eomonth('Date'[Date],0) = eomonth(today(),-0),GL[1_EXP] = "EXP")

it is not working. Hope some one can advise me.

Below link is my PBI file :-

https://www.dropbox.com/s/7kuxna8uqdjbu01/PBT_V2021_382%20TI_SI_GL%20how%20to%20get%20last%20month%2...

Paul

[jdbuchanan71 · Accepted solution]

Hello @admin11 

A couple of items first.  When you are filtering you want to avoid filtering an entire table, you want to filter only on the column so instead of.

AMOUNT_EXP =
CALCULATE ( SUM ( GL[AMT] ) * -1, FILTER ( GL, GL[1_EXP] = "EXP" ) )

You would want to use.

AMOUNT_EXP =
CALCULATE ( SUM ( GL[AMT] ) * -1, FILTER ( VALUES ( GL[1_EXP] ), GL[1_EXP] = "EXP" ) )

It has to do with table expansion in the vertipaq engine, suffice to say filtering a single column is much faster.

Further, for simple expressions like this you don't need the FILTER section, you can do it like this.

AMOUNT_EXP =
CALCULATE ( SUM ( GL[AMT] ) * -1, GL[1_EXP] = "EXP" )

And finally, you already have a measure that is the AMOUNT

AMOUNT = SUM(GL[AMT])*-1

So you can use that in your AMOUNT_EXP measure:

AMOUNT_EXP =
CALCULATE ( [Amount], GL[1_EXP] = "EXP" )

 Also, when you are referencing a measure in another measure like I am above, there is no need to put the table name in. [Amount], not 'GL'[Amount].  It makes it clear that [Amount] is a measure and not a column in a table.
Now, to your question on Current Month -2 vs -1, are you sure your -2 is working the way you want?  It seems to be only looking back to Feb.

Anyway, here are the measures for my Current - 1 and Current - 2

Current - 1 =
VAR _Start = EOMONTH ( TODAY (), -2 ) + 1
VAR _End = EOMONTH ( _Start, 0 )
RETURN
    CALCULATE ( [AMOUNT_EXP], DATESBETWEEN ( 'Date'[Date], _Start, _End ) )

Current - 2 =
VAR _Start = EOMONTH ( TODAY (), -3 ) + 1
VAR _End = EOMONTH ( _Start, 0 )
RETURN
    CALCULATE ( [AMOUNT_EXP], DATESBETWEEN ( 'Date'[Date], _Start, _End ) )

[admin11]

@jdbuchanan71 

To day when i refer to this post again , it help me create below expression , which i try to create for few week not successful , now finally make after view at how you create current month amount :-

_LYTD_REV_n =

var _max = date(year(today())-1,month(today()),day(today()))

return

TOTALYTD(('GL'[AMOUNT_REV]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

Above epression :-

if i don't select year and month it will display LYTD 

if i select year and month it will display respective amount.

Thank you

Paul

[admin11]

@jdbuchanan71 

Hope you can help me take a look of my post :-

https://community.powerbi.com/t5/Desktop/How-to-modify-my-LYTD-expression-So-that-i-can-get-Jan-2020...

Paul

[admin11]

@jdbuchanan71 

Finally i got the expression i need . Thank you very much for your help.

Paul