DAX Challange - Filtering Date by Difference between Consecutive Dates

[PowerRobots99]

Hello Friends,

Please refer .PBIX and excel file saved at path below :-

https://drive.google.com/drive/folders/1Fyp8iTgi_5pe3R_AsBD5QbbmUt608LXf?usp=sharinghttps://drive.go...

Required Output - 

I am facing a DAX challange where, we have to calculate the difference between consecutive report dates, and then filter "TransScheduleCompletionDate" by that difference.

For example, the difference between 07-28-2024 and 08-25-2024 is 28 days, 

Hence, we have to select report date - "07-28-2024" and then filter "TransScheduleCompletionDate" column to select next 28 days starting from 07/29/2024 (Inclusive) - (29 Jul-25 Aug) and then returning count of transactions 2502 against report date

08-25-2024

Thank you,

Happy learning

[shafiz_p]

Hi @PowerRobots99  Try below code:

For Report Date Difference

For CompletionDate:

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[PowerRobots99]

Hi @shafiz_p 

Thank you for your reply, appreciate it,

But, please read my question again, we need output in below form -

[shafiz_p]

@PowerRobots99  Try below code to get the desired output. See image below:

You can also create a new table from the above table by using Distinct and SelectColumns Function:

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,

Shahariar Hafiz

[PowerRobots99]

@shafiz_p 

Thank you for the reply,

Please provide DAX output in maxtrix visual as shown 

[shafiz_p · Accepted solution]

@PowerRobots99  Now you have column in your table as well as you know how to calculate scheduled trans. It is very easy to develop such matrix.

However, here is your desired output:

Code Version 1:

ScheduledTrans1 = 
IF(
    HASONEVALUE(Data[ReportDate]),
    MAX(Data[ScheduledTrans])
)

Image showing matrix:

Code Version 2:

DistinctScheduledTrans = 
IF(
    ISINSCOPE('Data'[ReportDate]),
    CALCULATE(
        MINX(
            'Data',
            [ScheduledTrans]
        )
    ),
    BLANK()
)

 Image showing matrix output version 2:

If you don't want to create a calculated column, then try the below code:

DistinctScheduledTrans_V3 = 
VAR CurrentReportDate = MAX('Data'[ReportDate])

VAR PreviousDate = 
    CALCULATE(
        MAX('Data'[ReportDate]),
        FILTER(
            ALL('Data'),
            'Data'[ReportDate] < CurrentReportDate
        )
    )

VAR DateDiff = 
    DATEDIFF(
        PreviousDate, 
        CurrentReportDate, 
        DAY
    )

VAR TransactionIDCount = 
    CALCULATE(
        COUNT('Data'[Transaction ID]),
        FILTER(
            ALL('Data'),
            'Data'[TransScheduleCompletionDate] >= PreviousDate + 1 &&
            'Data'[TransScheduleCompletionDate] <= PreviousDate + DateDiff &&
            'Data'[ReportDate] = PreviousDate
        )
    )

RETURN

IF(
    ISBLANK(PreviousDate),
    BLANK(),
    IF(
        HASONEVALUE(Data[ReportDate]),
        TransactionIDCount
    )
)

Here is the desired output:

Hope this helps!!