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.

Reply
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

YearDate_Cal(M/d/Y)AmountPlan
20183/1/2018 0:002595 
20184/1/2018 0:002402 
20185/1/2018 0:00299 
20186/1/2018 0:002949 
20187/1/2018 0:00299 
20188/1/2018 0:002819 
20189/1/2018 0:00273 
201810/1/2018 0:003524 
201811/1/2018 0:002970 
201812/1/2018 0:00284 
20191/1/2019 0:003007 
20192/1/2019 0:002865 
20193/1/2019 0:001999 
20194/1/2019 0:002741 
20195/1/2019 0:002806 
20196/1/2019 0:002908 
20197/1/2019 0:003122 
20198/1/2019 0:002954 
20199/1/2019 0:002724 
201910/1/2019 0:002870 
201911/1/2019 0:002998 
201912/1/2019 0:002675 
20201/1/2020 0:0030553118
20202/1/2020 0:009303105
20203/1/2020 0:00 3137
20204/1/2020 0:00 3142
20205/1/2020 0:00 3146
20206/1/2020 0:00 3166
20207/1/2020 0:00 3191
20208/1/2020 0:00 3184
20209/1/2020 0:00 3151
202010/1/2020 0:00 3144
202011/1/2020 0:00 3157
202012/1/2020 0:00 2914
2 ACCEPTED SOLUTIONS
amitchandak
Super User
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/

View solution in original post

v-lili6-msft
Community Support
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:

2.JPG

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.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

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

v-lili6-msft
Community Support
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:

2.JPG

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.
amitchandak
Super User
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.