How to modify my existing expression to alway display April 2021 amount ?

[admin11]

Hi All

I have below expression working fine , it will display last month PnL amount , that is April 2021 Amount.

Current month - 0 PnL =

VAR _MIN =

EOMONTH ( TODAY (), -2 ) + 1

VAR _MAX =

EOMONTH ( TODAY (), -1 )

RETURN

TOTALYTD (

( GL[AMOUNT] ),

'Date'[Date],

FILTER ( 'Date', AND ( 'Date'[Date] >= _MIN, 'Date'[Date] <= _MAX ) )

)

Now i like to hard code the April Amount. Can some one share with me how to modify the above expression , so that it will alway display April 2021 amount even during in June 2021.

My PBI file :-

https://www.dropbox.com/s/9dcps366sl3uwmv/PBT_V2021_400%20GL_TI.pbix?dl=0

Paul

[v-yalanwu-msft · Accepted solution]

Hi @admin11  ,  

If you want your expression to always display April 2021, you could modify it as follows:

Current month - 0 PnL2 =
TOTALYTD (
    ( GL[AMOUNT] ),
    'Date'[Date],
    FILTER ( 'Date', EOMONTH ( [Date], 0 ) = EOMONTH ( DATE ( 2021, 04, 01 ), 0 ) )
)

And if it's Mar 2021,you could change DATE (2021,04,01) to DATE (2021,03,01);

if it's Feb 2021, change DATE (2021,04,01) to DATE (2021,02,01);

and so on…

The final output is shown below:  

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[admin11]

@v-yalanwu-msft 

Thank you very much for your help , not many people understand what i am trying to said.

[amitchandak]

@admin11 , Try like

Current month - 0 PnL =
Var _1 = if(month(today()) >4, month(today()) -4, month(today()) +9)
VAR _MIN =
EOMONTH ( TODAY (), (-1* _1) -1 ) + 1
VAR _MAX =
EOMONTH ( TODAY (), (-1* _1) )
RETURN
TOTALYTD (
( GL[AMOUNT] ),
'Date'[Date],
FILTER ( 'Date', AND ( 'Date'[Date] >= _MIN, 'Date'[Date] <= _MAX ) )
)

here +9 can be +8

[admin11]

@amitchandak 

it work fine , Thank you very much for your help.

May i know how to modify the expression for Mar 2021 ? i have try play with from -1 to -2 it does not work.

Paul Yeo