Created Virtual Table to Provide a List of Open Items Scheduled to Closed within 90 Days

[TateAtWork]

Hi All,

I wrote the following DAX formula to return the items scheduled to close based on the range start date of the current date, and the end date equals the current date minus 90 days. When using the DAX Query View, my results are different than when I add the measure to a table using the calendar that has a relationship with the estimated closed date column from the open item table. I downloaded the Excel from DAX studio. The formula appears to do what I want, but I'm not confident because the values are not one-to-one. Here's the formula:

    /* Use the date loaded date as current date to create the window

     of open items*/

    VAR __snapshotDate = 

        MAX(

            'calendar_lookup'[Date]

            )

    /* Remove any external filters that will impact calculations */

    VAR __calculatedTable =

CALCULATETABLE(

'open_item',

FILTER(

'open_item',

                'open_item'[estimatedclosedate] >= __snapshotDate - 90 &&

                'open_item'[estimatedclosedate] <= __snapshotDate

),

'opportunity_stages'[Stage Name] = "Won"

)

    /* Filter table that spans from current date (filtered date) to

    previous 90 days and performing the desired calculations */

VAR __oppsMedianCycleTime =

CALCULATE(

        [Median Cycle Time],

        FILTER(

            __calculatedTable,

            RANKX(                                -- Use Rankx to remove duplicate item ids from the virtual table

                FILTER(

                    __calculatedTable,

                    'open_item'[item_id] = EARLIER('open_item'[item_id])

                ),

                'open_item'[dl_load_date],

                ,

                DESC,

                Dense

            ) = 1

        )

)

    RETURN

    __oppsMedianCycleTime

[Anonymous]

Hi @TateAtWork ,
You mentioned that the results in the DAX Query View are different from the results in the table visual object. This difference is usually related to a difference in context; the DAX Query View executes the query without relying on external context (e.g., rows, columns, slicers, etc.), whereas the metrics in the visual object are influenced by context, such as filters from other fields.

I'm not sure if your DAX formula is a measure or a calculated column, but if it is a measure, the earlier function won't work!

If you can provide some relevant example data or relevant pbix files, it may help to help you solve the problem faster, if you still have further questions, you can feel free to contact me, I will get back to you as soon as I receive the message!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[ahadkarimi]

Hi @TateAtWork, give this a try, and if you encounter any issues, let me know.

VAR __calculatedTable =
CALCULATETABLE(
'open_item',
DATESBETWEEN(
'calendar_lookup'[Date],
__snapshotDate - 90,
__snapshotDate
),
'opportunity_stages'[Stage Name] = "Won"
)

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Specialist!