Target Actual MTD QTD YTD For Complete Month

[Anonymous]

I have questions related to calculate all complete month value. I have tried so many different things and can't find a solution. I understand the concept of MTD/YTD/QTD totals. But with target and actual I am really confused how to use the same date column to calculate MTD/YTD/QTD and PMTD/PQTD/PYTD.

The current max date is Feb/1/2020. I want to calcuate values in Jan( a completed month). 

Jan 2020, MTD, QTD, YTD, and Jan 2019 MTD, QTD, YTD. then when Feb is completed, the fields should automatically show Feb 2020 and Feb 2019, not March(March will be current)... Also I want to show the Jan 2020 Plan MTD value. 

Thank you so much

Year	Date_Cal(M/d/Y)	Amount	Plan
2018	3/1/2018 0:00	2595
2018	4/1/2018 0:00	2402
2018	5/1/2018 0:00	299
2018	6/1/2018 0:00	2949
2018	7/1/2018 0:00	299
2018	8/1/2018 0:00	2819
2018	9/1/2018 0:00	273
2018	10/1/2018 0:00	3524
2018	11/1/2018 0:00	2970
2018	12/1/2018 0:00	284
2019	1/1/2019 0:00	3007
2019	2/1/2019 0:00	2865
2019	3/1/2019 0:00	1999
2019	4/1/2019 0:00	2741
2019	5/1/2019 0:00	2806
2019	6/1/2019 0:00	2908
2019	7/1/2019 0:00	3122
2019	8/1/2019 0:00	2954
2019	9/1/2019 0:00	2724
2019	10/1/2019 0:00	2870
2019	11/1/2019 0:00	2998
2019	12/1/2019 0:00	2675
2020	1/1/2020 0:00	3055	3118
2020	2/1/2020 0:00	930	3105
2020	3/1/2020 0:00		3137
2020	4/1/2020 0:00		3142
2020	5/1/2020 0:00		3146
2020	6/1/2020 0:00		3166
2020	7/1/2020 0:00		3191
2020	8/1/2020 0:00		3184
2020	9/1/2020 0:00		3151
2020	10/1/2020 0:00		3144
2020	11/1/2020 0:00		3157
2020	12/1/2020 0:00		2914

[amitchandak · Accepted solution]

With Datesmtd or total mtd you can mix endof* to get the complete month, qtr or year. Check examples

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

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[amitchandak]

@Anonymous , Two solution have been provided. Check if something can work out for you?

[v-lili6-msft · Accepted solution]

HI  @Anonymous 

You could use this formula to achieve it

MTD = var _currentmonth=CALCULATE(MAX('Date'[Date]),FILTER('Table','Table'[Amount]<>BLANK())) 
var _nextmonth=CALCULATE(MIN('Table'[Date_Cal(M/d/Y)]),FILTER(ALL('Date'),'Date'[Date]>MAX('Table'[Date_Cal(M/d/Y)])),'Table'[Amount]<>BLANK())
return
IF(_currentmonth<_nextmonth,TOTALMTD(SUM('Table'[Amount]),'Date'[Date]))

QTD = var _currentmonth=CALCULATE(MAX('Date'[Date]),FILTER('Table','Table'[Amount]<>BLANK())) 
var _nextmonth=CALCULATE(MIN('Table'[Date_Cal(M/d/Y)]),FILTER(ALL('Date'),'Date'[Date]>MAX('Table'[Date_Cal(M/d/Y)])),'Table'[Amount]<>BLANK())
return
IF(_currentmonth<_nextmonth,TOTALQTD(SUM('Table'[Amount]),'Date'[Date]))

YTD = var _currentmonth=CALCULATE(MAX('Date'[Date]),FILTER('Table','Table'[Amount]<>BLANK())) 
var _nextmonth=CALCULATE(MIN('Table'[Date_Cal(M/d/Y)]),FILTER(ALL('Date'),'Date'[Date]>MAX('Table'[Date_Cal(M/d/Y)])),'Table'[Amount]<>BLANK())
return
IF(_currentmonth<_nextmonth,TOTALYTD(SUM('Table'[Amount]),'Date'[Date]))

Result:

Of course, to get the best of the time intelligence function. 

here is sample pbix file, please try it.

Regards,

Lin

[amitchandak · Accepted solution]

With Datesmtd or total mtd you can mix endof* to get the complete month, qtr or year. Check examples

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

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/