Need help calculating the date where a measure value meets or exceeds a ceiling

[rpiboy_1]

First, I'll admint that I've been using an AI tool to assist me with this measure, but I have not been blindly copying and pasting. I've been writing the code and attempting to make sure I understand what the code is doing, which is why I've arrived at the point where I need real human input and not a 'robot'.

The goal, a measure that returns the number of days from when a version is first seen in the Usage Data (in lieu of a release date) to the date when 50% (or more) of the projects are using that particular version. I want two versions of the measure, one which accounts for 'active projects only' that is projects that have had at least one login from any version within the timeframe context and another version which is calculated based on all Projects in the data, regardless of activity.

Data model:
Fact Usage Data - each row logs the quantity of logins for a given project on a given month from a given version of a software application (the data is aggregated to the 1st of the month).
Dim Date - well formed date table

Dim Client Versions - each row represents a unique version of the application, includes columns for 'first Seen Date', as a stand-in for 'release date'

Dim Projects - each row represents a unique project

There are one to many relationships between the Fact table and each dimension table, with single direction filtering.

Code that I have so far that each piece 'works' in terms of returning the anticiapted value.

T50 Adoption Days Active Projects = 
VAR _versionRelease = SELECTEDVALUE('Cient Versions'[First Seen Date])

/*VAR _totalProjs =
    CALCULATE(
        COUNTROWS('Projects'),
        ALLSELECTED('Projects')
    )*/

VAR _totalActiveProjs =
    CALCULATE(
        DISTINCTCOUNT('Usage Data'[region.project_id]),
        'Usage Data'[Login Count] >= 1,
        ALLSELECTED('Cient Versions'),
        ALLSELECTED('Date')
    )

VAR _halfProj = CEILING(0.5 * _totalActiveProjs, 1)

You'll note that I have an alternate variable to address 'all' projects versus active projects. This works, I've retained it to make it easier to split into two measures later.

Where I'm stuck; conceputally I understand what I need to do, its execution that is lacking.

I need to count the total number of projects with at least one login for for each date so that I can compare that count to the threshold to determine if that count exceeds the threshold of being 50% or greater of the projects. I have a measure that properly tablulates the quantity of projects. It is assumed that the report/visual context will supply the version and date range that the measure is being calculated for.

Version Logins (Project) = 
CALCULATE(
    DISTINCTCOUNT( 'Usage Data'[region.project_id] ),
    'Usage Data'[Login Count] >= 1
)

Where I'm failing is how to properly apply the measure to iterate over the date context to identify at what date the count exceeds the threshold.

Once I can return the date where the count of projects exceeds 50% of total projects, then the remainder of the measure is relatively straight-forward and I have that too:

VAR _result =
    IF(
        NOT ISBLANK(_versionRelease) && NOT ISBLANK(_t50Date),
        DATEDIFF(_versionRelease, _t50Date, DAY),
        BLANK()
    )

I imagine that once someone explains it to me, I'll understand it, but I just can't formulate it myself, and that is just where I am in my DAX journey. I should also note that I'm not sure what 'typical' pattern I should look at for insight as to how to go about this calculation. Recently I've been pretty succesful looking at existing documented patterns and adapting them, but this one I'm not sure where to start.

Thanks so much!

[johnt75]

I think you can use

VAR SummaryTable =
    ADDCOLUMNS (
        VALUES ( 'Date'[Start of month] ),
        "@num projects",
            CALCULATE (
                DISTINCTCOUNT ( 'Usage Data'[region.project_id] ),
                'Usage Data'[Login Count] >= 1,
                ALLSELECTED ( 'Cient Versions' )
            )
    )
VAR TableWithRunningTotal =
    ADDCOLUMNS (
        SummaryTable,
        "@running total",
            VAR CurrentDate = 'Date'[Start of month]
            VAR Result =
                SUMX (
                    FILTER ( SummaryTable, 'Date'[Start of month] <= CurrentDate ),
                    [@num projects]
                )
            RETURN
                Result
    )
VAR DateRequired =
    MINX (
        FILTER ( TableWithRunningTotal, [@running total] >= _halfProj ),
        'Date'[Start of Month]
    )

This builds a list of dates ( I'm assuming that you have a date type column in the date table showing the start of the month ), and for each dates uses your existing code to calculate the number of active projects. It then calculates the running total for this summary table, and finds the first date where the running total exceeds your threshold value.

[rpiboy_1]

@johnt75 thanks for the reply. I don't think I need/want a running total, generally speaking for the software in question once a user is using a 'newer' version they don't go back, and I can see in the data that new versions (relatively quickly) overtake older versions, so I'm just trying to quantify that 'rate' of conversion. If anything I may want to up calculating the reverse to assess how long older versions linger (but that is a different conversation).

In regards to the code you did provide, if I don't want to do a running total, it seems like I should be able to just use [@num projects] in the VAR DateRequired; would you agree?

Thanks again!

[johnt75]

Yes, that would make sense and make the code easier to read and more efficient too.

[rpiboy_1 · Accepted solution]

@johnt75 your solution didn't quite work, but it did put me on the path to getting to something that did work! I ended up with something relatively simple and straight-forward, just had to get there:

VAR SummaryTable =
    SUMMARIZECOLUMNS(        
        'Date'[Date],
        "@ProjectCount", [Version Logins (Project)]
    )

Since the context will supply version, all I need to do was calculate the Measure I already had over dates. Then I can find the minx date from the resulting table.

Simple and easy, as they say DAX is easy AND hard... 🙂

[v-menakakota]

Hi @rpiboy_1 ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.

Could you please provide sample data and expected output for the scenario which could be helpfull to resolve the issue.

Best Regards, 
Menaka.
Community Support Team