Forum Discussion
Previous month Target
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?
2 Replies
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))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
