Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Target Actual MTD QTD YTD For Complete Month

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
2 ACCEPTED SOLUTIONS
Super User

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/

Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User

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

Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

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/

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors