DAX Query for MTD , QTD and YTD number days count for multiple criteria.

[proavinash]

1. Situation :-  when nothing is selected it should visible MTD days, QTD days and YTD days. Which is visible correct below Screen shot.

2. Situation :-  when current year is selected it should visible MTD days, QTD days and YTD days. Which is current MTD, YTD AND YTD visible which is also correct below Screen shot.

3. Situation :-  when current Quarter is selected it should visible MTD days, QTD days and YTD days. Which is current MTD, YTD AND YTD visible which is also correct below Screen shot.

4. Situation :-  when back Quarter is selected means either Qtr-1, Qtr-2 or Qtr-3, it should visible as full month til MTD days, full Quarter till date QTD days and and Full Year till date YTD days. Which is current MTD, YTD AND YTD visible in below screen shot is incorrect below Screen shot.

1. When Qtr-1 , MTD days = would be Month Mar days count,  QTD days = would be Full quarter Jan+Feb+Mar number of days, and YTD days  = would be full YTD , Jan+Feb+Mar number of days,
2. When Qtr-2  , MTD days = June days could ,  QTD days = Apr+May+Jun ,   YTD days = Jan+Feb+Mar+ Apr+May+Jun.
3. When Qtr-3 , MTD days = Sep days could ,  QTD days = Jul+Aug+Sep ,   YTD days = Jan+Feb+Mar+ Apr+May+Jun+ Jul+Aug+Sep.

So, below numbers as per Qerry   4 & (1, 2, 3) is incorrect need the solution.

5. Situation :-  when month is selected it should visible MTD days, QTD days and YTD days. Which is current MTD, YTD AND YTD visible which is incorrect below screen shot.

1. When January is selected MTD days would be = 31, QTD days = 31, YTD days = 31.

2. When Feb is selected MTD days would be = 28, QTD days = Jan+Feb, YTD days = Jan+Feb.

3. When Mar is selected MTD days would be = 31, QTD days = Jan+Feb+Mar, YTD days = Jan+Feb+Mar.

4. When Apr is selected MTD days would be = 30, QTD days = 30, YTD days = Jan+Feb+Mar+Apr.

5. When May is selected MTD days would be = 31, QTD days = 31, YTD days = Jan+Feb+Mar+Apr+May.

6. When Jun is selected MTD days would be = 30, QTD days = 31, YTD days = Jan+Feb+Mar+Apr+May+Jun.

7. When Jul is selected MTD days would be = 31, QTD days = 31, YTD days = Jan+Feb+Mar+Apr+May+Jun+Jul

8. When Aug is selected MTD days would be = 31, QTD days = 31, YTD days = Jan+Feb+Mar+Apr+May+Jun+Jul+Aug.

9. When Sep is selected MTD days would be = 30, QTD days = 31, YTD days = Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep

10. When Oct is selected MTD days would be = 31, QTD days = 31, YTD days = Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct.

11. When Nov is selected MTD days would be = 30, QTD days = 31, YTD days = Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov.

12. When Dec is selected MTD days would be = 31, QTD days = 31, YTD days = Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec.

6. Situation :-  when particular date  is selected it should visible MTD days, QTD days and YTD days. Which is current MTD, YTD AND YTD visible which is incorrect below Screen shot.

1. Like 05-Jan-2024 selected then MTD, QTD YTD, days would be till date.   MTD days = month till date 5-Jan-2024,  QTD days = Quarter till date 5-Jan-2024, YTD days = year till date 5-Jan-2024, same as for each quarter.

7. Last query is for Back Year hierarchy working is same as current year,  back year means not only last year but also all back year available in database.

[Anonymous · Accepted solution]

Hi @proavinash ,

I think you can try TOTALMTD()/TOTALQTD()/TOTALYTD() function to achieve your goal. You need to create a dimdate table with continuous day for time intelligence function.

SelectDate = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2024, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Quarter",
        "Qtr" & " "
            & QUARTER ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthSort", MONTH ( [Date] ),
    "Day", DAY ( [Date] )
)

Relationship:

Measures:

MTD = TOTALMTD(DISTINCTCOUNT(TestDate[Date]),SelectDate[Date])

QTD = TOTALQTD(DISTINCTCOUNT(TestDate[Date]),SelectDate[Date])

YTD = TOTALYTD(DISTINCTCOUNT(TestDate[Date]),SelectDate[Date])

Result is as below.

Best Regards,
Rico Zhou

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

[proavinash]

if possible then , make a solution with my date table no other table using because of lots of measure already created by using my date table which is get effected when create other date table and make relation, so if possible then make a solution with my date table , format of my date table screen shot below. please help.

[proavinash]

Your solution is working perfectly  , but I need solution of problem below when apply your solution I have an error in some of my measure in which I calculate the number of unique customer who has billed in particular month in which date condition apply. See below screen shot.

Error in my date filed which is under SaleDump Table when make relation with Selecteddate

When mouse cursor over on red line error on "SaleDump[Date].[Date]" 

it says "Parameter is not the correct type" when SaleDump[Date] part

and when .[Date] part it says  "Parameter is not the correct type with cannot find name [Date]"