Previous month Target

[APALLIPATH]

Jan MTD Sales and Target TD is done and it worked perfectly. MTD sales is updated and the given Target for the month is taken and MTD Target is calculated by checking the calendar. But when we reached Feb, Januaray MTD is taken as full month, but Target is taken as per the calendar date.

Eg: On 31.01.2021

Sales Jan 21: 100

Target Jan 21: 110

Achievement Index: 91

This way, January month is closed.

In February, 10.02.2021, when I check January sheet

Sales Jan 21: 100

Target Jan 21: 110 divided by 31 days of Jan multiply by 10 (date of Feb) - 35.5

Achievement Index: 281

On 10.02.2021

Sales Feb 21: 32

Target Feb 21: 35.7 (Total target is 100 divided by 28 multiplied by 10)

Index: 90

How to keep January achievement Vs Target as the closing figure? 

[Anonymous]

Hi @APALLIPATH,

If you want to use a specific month as the filter to calculate with other months, you can create a variable to extract and stored it, then you can invoke this variable in other calculations of the current expression. (you can use the max function to get the last date and use it as a condition to filter records)

BTW, can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng

[amitchandak]

@APALLIPATH , Please check the attached file, if that can help. It only gives MTD

Last month

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

MTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESMTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESMTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALMTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))