topic MTH and YTD Sales in DAX Commands and Tips

MTH and YTD Sales

Justin27 — Mon, 06 Jun 2022 05:11:34 GMT

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..

Re: MTH and YTD Sales

amitchandak — Mon, 06 Jun 2022 09:19:20 GMT

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-bd52912a5bd4
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.

Re: MTH and YTD Sales

Anonymous — Thu, 09 Jun 2022 09:55:58 GMT

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-power-bi/

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

Best Regards,

Jay