How to modify expression return Feb 2021 Amount ?

[admin11]

Hi All

I have below YTD expression working fine :-

_YTD EXP = CALCULATE(CALCULATE(GL[AMOUNT],DATESYTD('Date'[Date],"12/31")), GL[1_EXP] = "EXP")

Can some one help me to modify the above expression to return Feb 2021 amount .

My PBI file :-

https://www.dropbox.com/s/wraj6wobp258b8z/PBT_V2021_367%20EXP%20YTD%20how%20to%20return%20Feb%202021...

Paul

[mohammedadnant]

Hi @admin11 

Good day,

Please check this video might help,  https://youtu.be/WiuXJGWwong

Thanks & Regards,

Mohammed Adnan

https://www.youtube.com/c/taik18

[admin11]

@mohammedadnant 

i Just finish watching the youtube.

I try to change the Y to M :-

_MTD EXP = CALCULATE(CALCULATE(GL[AMOUNT],DATESMTD('Date'[Date],"12/31")), GL[1_EXP] = "EXP")

I get error msg :-

[mohammedadnant]

HI @admin11 

Try this 2 formulas..

TOTALMTD(GL[AMOUNT], 'DATE'[DATE], ALL('DATE'[DATE]), GL[1_EXP] ="EXP")

TOTALYTD(GL[AMOUNT], 'DATE'[DATE], ALL('DATE'[DATE]), GL[1_EXP] ="EXP", "02/28")

Thanks & Regards,

Mohammed Adnan

[admin11]

@mohammedadnant 

Thank you for your sharing .

The first expression , i get error msg :-

Too many arguments were passed to the TOTALYTD function. The maximum argument count for the function is 4.

the second expression , i get error msg :-

Too many arguments were passed to the TOTALYTD function. The maximum argument count for the function is 4.

Paul Yeo

[mohammedadnant]

hi @admin11 

Can you share the pbix file..

Thanks & Regards,

Mohammed Adnan

[admin11]

@mohammedadnant 

My PBI file :-

https://www.dropbox.com/s/wraj6wobp258b8z/PBT_V2021_367%20EXP%20YTD%20how%20to%20return%20Feb%202021...

Above link is my PBIX file 

Paul

[V-lianl-msft]

Hi @admin11 ,

Your formula is used to calculate YTD. If you want to calculate the value of February, it is recommended that you apply filters to [amount].

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[admin11]

@V-lianl-msft 

Thank you for sharing.

The reasons i need it with out using filter panel is because , i need it dynamic change every month with me change the filter panel setting. 

Paul

[V-lianl-msft]

Hi @admin11 ,

Do you want to display the sum of the most recent months in the fact table with a card?

Try this measure:

Measure = var min_ = CALCULATE(MAX(GL[Posting Date]),ALLSELECTED('Date'))
var ym = FORMAT(min_,"YYYY-MM")
return CALCULATE(GL[AMOUNT],FILTER(GL,GL[1_EXP]="EXP"),FILTER('Date','Date'[YrMth Sort]=ym))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[admin11]

@V-lianl-msft 

Thank you very much for your sharing. it work fine on the simple file , But when i try to test with my raw data , i notice that it does not work. it display blank , Hope you can take a look.

https://www.dropbox.com/s/9c0c1n12y12vbfp/PBT_V2021_381%20Sample%20Data%20ONLY%20ID%20GL%20get%20feb...

Above is my PBI file 

Paul