MTH and YTD Sales

[Justin27]

I have a excel datasouce from Col A to Col Z, Col. X contains the monthly data in MM.YYYY format.

In the Dashbard I need to add dropdown or check box as "MTD" Month to Date, and "YTD" Year to Date.

In Excel we used to write a formula to provide the MTD based on the month from Current date function and for Year to date being less than and equal to month from curent date function.

Now we are migrating to Power BI and need to know if we can implement this in excel.

Thanks & Regards,

Justin..

[Anonymous]

Hi @Justin27 ,

Are you want calculate the MTD and YTD in Power BI?

If so, you could refer this blog:

https://www.mssqltips.com/sqlservertip/4841/calculating-mtd-qtd-ytd-running-and-cumulative-total-in-... 

If you want detailed formula, please share some sample data and expected result.

Best Regards,

Jay

[amitchandak · Accepted solution]

@Justin27 , create a date using MM.YYYY

Date =  #date(right([Month],4), left([Month,2),1)

Then you can use time intelligence with date tbale

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

or today vs max selected

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Refer the code with slicer

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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 :radacad sqlbi My Video Series Appreciate your Kudos.