Transform measure in cumulative measure

[cdebackere]

I Have a 'progress' measure based on 2 related tables (linked over a factless fact table) to measure the number of reservation allready present for the current period, compared to the total reservations of a reference period.

This measure works seamingly well:

rooster.progress.25-26-JH1 =

VAR AY="2025-26"

VAR refAY="2024-25"

VAR JH="JH1"

VAR resTblCnt = CALCULATE(COUNTROWS(allReservations),

    'course'[course.academic.year] = AY &&

    'course'[course.academic.year.part] = JH

)

VAR resRefTblCnt = CALCULATE(COUNTROWS(allReservations),

    'course'[course.academic.year] = refAY &&

    'course'[course.academic.year.part] = JH

)

RETURN

ROUND(IF(resTblCnt=0,0,IF(resRefTblCnt=0,5,resTblCnt / resRefTblCnt)),2)

Now this gives the current situation, like shown below visual:

I want to also visualise how the progress evolves over time, so how/when we got to the current state. So it will start at 0% and move up till the current % of today (and eventually to ~100%). The timeline axis of the graph would be allReservations[created] which is of type DateTime

I tried the following small alteration (in bold), but the XY graph goes up and down, no way it's cumulative. I don't see what I'm doing wrong

rooster.progress.25-26-JH1.cumul =

VAR AY="2025-26"

VAR refAY="2024-25"

VAR JH="JH1"

VAR allResetRes = FILTER(ALL(allReservations),allReservations[created]<=MAX(allReservations[created]))

//I also tried this: VAR allResetRes = FILTER(ALLEXCEPT(allReservations,allReservations[created]),allReservations[created]<=MAX(allReservations[created]))

VAR resTblCnt = CALCULATE(COUNTROWS(allResetRes),

    'course'[course.academic.year] = AY &&

    'course'[course.academic.year.part] = JH

)

VAR resRefTblCnt = CALCULATE(COUNTROWS(allReservations),

    'course'[course.academic.year] = refAY &&

    'course'[course.academic.year.part] = JH

)

RETURN

ROUND(IF(resTblCnt=0,0,IF(resRefTblCnt=0,5,resTblCnt / resRefTblCnt)),2)

The idea is that I reset the allReservations table to 'full view', and then filter to all prior to the date of the measure/point on the graph. But I get:

(there is also a legend configured, which is a column from the allReservations table, which the same column used in the horizontal category in the good 'static' graph above)

Where am I missing out?

BR,

Christof

[cdebackere]

Hi @DataNinja777 

Based on what you suggest I did:

- mark my Date table a a proper date table. Despite the warning I don't have the impression it changed anything to my reports.

- trie your suggested solution, and some variant, because it didn't work out.

The positieve: on certain formulas I get an endresult in the expected range: 0 to ~100% and going upto 500%, as per fomula in case ne previous period data is available.

But the graph looks wrong:

this is when using what you suggest

Do note:

 - the x-axis is Date[Date]

 - the Date[Date] is related to allReservations[Date] and not to allReservation[created]

 - the cumulative calculation should be on the create date of the reseration [created], and not on the actual reserved day in allReservations[Date]

I tried to alter the above to reflect that with no succes

VAR ResTblCumul =
    CALCULATE(
        COUNTROWS(allReservations),
        FILTER(
            ALL('Date'),
            'Date'[Date] <= CurrentDate
        ),
        'course'[course.academic.year] = AY,
        'course'[course.academic.year.part] = JH,
        allReservations[created] <= CurrentDate,
    )

but that resulted in 'query exceeded resources, please ..'

[v-lgarikapat]

Hi @cdebackere ,

Thanks for reaching out to the Microsoft fabric community forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Best Regards,

Lakshmi Narayana

[v-lgarikapat]

 Hi @cdebackere ,

If your issue has been resolved, please consider marking the most helpful reply as the accepted solution. This helps other community members who may encounter the same issue to find answers more efficiently.

If you're still facing challenges, feel free to let us know we’ll be glad to assist you further.

Looking forward to your response.

Best regards,
LakshmiNarayana.

[v-lgarikapat]

Hi @cdebackere ,

If your issue has been resolved, please consider marking the most helpful reply as the accepted solution. This helps other community members who may encounter the same issue to find answers more efficiently.

If you're still facing challenges, feel free to let us know we’ll be glad to assist you further.

Looking forward to your response.

Best regards,
LakshmiNarayana.

[v-lgarikapat]

Hi @cdebackere ,

As we haven't heard back from you, we are closing this thread. If you are still experiencing the issue, please feel free to create a new thread we’ll be happy to assist you further.

Thank you for your patience and support.

If you found our response helpful, please mark it as Accepted Solution so others with similar queries can find it easily.

Best Regards,

Lakshmi Narayana

[cdebackere]

Done

[DataNinja777]

Hi @cdebackere ,

To create a cumulative progress measure that behaves correctly over time, the key is to ensure the measure evaluates all reservations up to the current point on the timeline. The issue in your original attempt stems from how MAX(allReservations[created]) behaves within the visual—it only considers the currently visible filter context, which can lead to inconsistent or non-cumulative results.

A better approach is to use a proper 'Date' table on the X-axis and refer to its current date context for consistent cumulative evaluation. Here's the revised measure:

rooster.progress.25-26-JH1.cumul =
VAR AY = "2025-26"
VAR refAY = "2024-25"
VAR JH = "JH1"

VAR CurrentDate = MAX('Date'[Date])

VAR ResTblCumul =
    CALCULATE(
        COUNTROWS(allReservations),
        FILTER(
            ALL(allReservations),
            allReservations[created] <= CurrentDate
        ),
        'course'[course.academic.year] = AY,
        'course'[course.academic.year.part] = JH
    )

VAR ResRefTotal =
    CALCULATE(
        COUNTROWS(allReservations),
        'course'[course.academic.year] = refAY,
        'course'[course.academic.year.part] = JH
    )

RETURN
ROUND(
    IF(ResTblCumul = 0, 0,
        IF(ResRefTotal = 0, 5,
            DIVIDE(ResTblCumul, ResRefTotal)
        )
    ),
2)

This measure ensures the cumulative progress for 2025-26 JH1 builds over time using the reservation created date, while always comparing it against the full count for the reference year 2024-25 JH1. The use of ALL(allReservations) clears any date filters that the visual applies to created, so you can accurately accumulate values up to the current date from the 'Date' table.

Best regards,

[cdebackere]

Hi @DataNinja777 

Thx for directing me in the right way.

In fact, I do have a Date table in my model already, so it was easy enough to adopt your code.

The good thing: where in the the initial time graph, the values were no way near reality (percentages up to 200k), they now only go up to 'only' 1200%

The bad thing: the graph still doesn't look like a cumulative graph at all, at going to values way above 100%:

Maybe I know the reason: I've been struggling with my Date table. 

In fact I added this Date table but probaly didn't set it up 'PowerBI wise', due to lack of understanding.

I made a relation between my Date table and allReservations[Date]. When I later tried to declare my Date table as the 'datamodel date table for PowerBI', I received some error/warning message I couldn't interprete at the time, so I left it as is .. so just related directly to the allReservations , because that's the main Date used in the reports, so it 'did the trick'.

I guess this is where I messed up, and that this is maybe now causing the miscalculation in the measure.

I was tempted to ask : "how should I deal with this Date table', but maybe this will me considered crossposting ... so I just 'hint' to it, in the context of the topic addressed in this treat.

C.

[DataNinja777]

Hi @cdebackere ,

You're seeing percentages shoot up to 1200% because your Date table isn't properly integrated into the model, which causes your measure to count the same reservations multiple times or evaluate incorrectly. The likely issue is that the Date table isn't marked as a proper date table or isn't filtering the allReservations table as expected. To fix this, first ensure your Date table has a column of type Date with unique values and no gaps. Then, in Power BI Desktop, right-click on the Date table, select "Mark as Date Table," and choose the correct date column. Create a one-to-many relationship from 'Date'[Date] to allReservations[created].

After fixing the relationship and marking the Date table, revise your measure to use the 'Date'[Date] column in the cumulative filter logic instead of filtering directly on allReservations. This ensures the measure respects the timeline from the Date table and avoids duplication. Use this version of the measure:

rooster.progress.25-26-JH1.cumul =
VAR AY = "2025-26"
VAR refAY = "2024-25"
VAR JH = "JH1"

VAR CurrentDate = MAX('Date'[Date])

VAR ResTblCumul =
    CALCULATE(
        COUNTROWS(allReservations),
        FILTER(
            ALL('Date'),
            'Date'[Date] <= CurrentDate
        ),
        'course'[course.academic.year] = AY,
        'course'[course.academic.year.part] = JH
    )

VAR ResRefTotal =
    CALCULATE(
        COUNTROWS(allReservations),
        'course'[course.academic.year] = refAY,
        'course'[course.academic.year.part] = JH
    )

RETURN
ROUND(
    IF(ResTblCumul = 0, 0,
        IF(ResRefTotal = 0, 5,
            DIVIDE(ResTblCumul, ResRefTotal)
        )
    ),
2)

This version ensures the calculation accumulates correctly over time by relying on the Date table's context and filtering. Once this setup is correct, your line chart should start from 0 and build steadily toward the final progress percentage without overshooting.

Best regards,

[cdebackere · Accepted solution]

Hi @DataNinja777 

I finally got it to work.

Due to the strange value, I made a temporary measure with just the resTblCnt and resRefTblCnt .. just to see that even the resRefTblCnt was wrong.

What seems to work is:

VAR resTblCnt = CALCULATE(
    COUNTROWS(allReservations),
    ALL('Date'),
    'allReservations'[created]<= CurrentDate,
    'course'[course.academic.year] = AY ,
    'course'[course.academic.year.part] = JH
)
VAR resRefTblCnt = CALCULATE(COUNTROWS(allReservations),
    ALL('Date'),
    'course'[course.academic.year] = refAY ,
    'course'[course.academic.year.part] = JH
)

So I added the ALL also to the RefCount .. which makes sence since the Ref is the total of the past, irrespective the date created

And similar for the Count, but adding the created date as a seperate filter condition in the CALCULATE rather than in a FILTER(ALL... construct.

Massive thanks for the insights, and getting me on track to achieve this result:

BR Christof

[cdebackere]

Oh, and I just realise the beauty of this measure: I can remove the 'non cumulative' version of it. Because when using the cumulative (Date dependant) measure in a visual without date.. then it defaults in behaviour to the end total metric. And you can even put a Date filter if you want to see the result at a given moment in time!

Cheers