Dax Function when select a date or months or Quarter we will get Number of days MTD, QTD and YTD

[proavinash]

Need Dax Function when select a date or month or 2 months or Quarter we will get Number of days MTD, QTD and YTD according to current date.

for an example , 

if we select 1-May-2024 from dropdown or from slicer of date range and today is 25-May-2024 then it will give MTD days number is 25 days, QTD days number is 55 days, and YTD days number is near about 145,

Selected Date  =  1-May-2024

Today Date = 25-May-2024

MTD = 25  ,    QTD = 55   and   YTD = 145

[Kedar_Pande]

@proavinash 

MTD = 
CALCULATE(
COUNTROWS('DateTable'),
'DateTable'[Date] <= TODAY(),
'DateTable'[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
)

QTD = 
CALCULATE(
COUNTROWS('DateTable'),
'DateTable'[Date] <= TODAY(),
'DateTable'[Date] >= DATE(YEAR(TODAY()), FLOOR(MONTH(TODAY())-1, 3)+1, 1)
)

YTD = 
CALCULATE(
COUNTROWS('DateTable'),
'DateTable'[Date] <= TODAY(),
'DateTable'[Date] >= DATE(YEAR(TODAY()), 1, 1)
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[proavinash]

Thanks for your help, but this is not working because of i have large database in which near about 20 lacs of line item so countrows not give a proper solution, in a month either 31, 30 or 28 days, and countrows count all rows in a month or year, 

[xifeng_L]

Hi @proavinash ,

You can use the DATESMTD, DATESQTD and DATEYTD functions to calculate the corresponding number of days, for example:

MTD Days = COUNTROWS(DATESMTD('Calendar'[Date]=TODAY()))
QTD Days = COUNTROWS(DATESQTD('Calendar'[Date]=TODAY()))
YTD Days = COUNTROWS(DATESYTD('Calendar'[Date]=TODAY()))

Then if you want to then control the returned result based on the number of items selected by the user, then you can slip in another IF function.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[proavinash]

Thanks for your help, but this is not working because of i have large database in which near about 20 lacs of line item so countrows not give a proper solution, in a month either 31, 30 or 28 days, and countrows count all rows in a month or year, 

[Anonymous · Accepted solution]

Hi @proavinash ,

I recommend that you use the DATEIFF() function, which is used to calculate the difference between two dates.

We can create three measures.

MTD = 
var _date1=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
var _date2=TODAY()
RETURN DATEDIFF(_date1,_date2,DAY)+1

QTD = 
var _date1=DATE(YEAR(TODAY()), FLOOR(MONTH(TODAY())-1, 3)+1, 1)
var _date2=TODAY()
RETURN DATEDIFF(_date1,_date2,DAY)+1

YTD = 
var _date1=DATE(YEAR(TODAY()), 1, 1)
var _date2=TODAY()
RETURN DATEDIFF(_date1,_date2,DAY)+1

Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[proavinash]

Thank you so much, it's working.