cumulative revenue measure based on calender filter selection

[sabrinekalbousi]

Hello @PowerBI team

In my Power Bi report, I have this measure calculating the cumulative revenue. The 'CalendarStart2020V' is the main calendar dimension, and the 'CalendarFilter' is just used as a slicer filter on the main page. the displayed date depending on the selected month, year on the calendar filter. Using this measure, if I select Dec 2024, the cumulative result is correct. However, when I change it to Nov 2024, the result is wrong. I didn't figure out how to fix it; any help will be really appreciated.

Total Charter Net Revenue Gap running total in DateId MTD =

(

    VAR _lastDate = IF(TODAY()<MAX('CalendarFilter'[DateId]), TODAY(),MAX('CalendarFilter'[DateId]))

     VAR _firstDate = DATE(YEAR(_lastDate),MONTH(_lastDate),1)

    RETURN

CALCULATE(

    ([Revenue (Net Bookings - Done) MTD]+ [Revenue (Net Bookings - To do) MTD]) - [Total Charter Budget MTD],

       FILTER(

        ALLSELECTED('CalendarStart2020V'[DateId]),

ISONORAFTER('CalendarStart2020V'[DateId], MAX('CalendarStart2020V'[DateId]), DESC)))

))

PS: I have tried to replace ISONORAFTER by filter between the two variables,but it is not the correct solut

[sabrinekalbousi]

Hi @v-heq-msft, thank you for your reply. This solution doesn't work. It cannot calculate the cumulative revenue day by day.

Here is an example of what I am expecting to get when I select "December 2024" for the slicer ( the cumulative graph day by day, and the table is just to show you an example of the data behind it).

[v-heq-msft · Accepted solution]

Hi @sabrinekalbousi ,
Depending on the data you provide, you can create 2 MEASURES to implement this process

Cumulative Revenue Difference = 
VAR CurrentDay = MAX('Table'[Day])
RETURN
SUMX(
    FILTER(
        ALL('Table'),
        'Table'[Day] <= CurrentDay
    ),
    ('Table'[Revenue (Net Bookings - Done) MTD] + 'Table'[Revenue (Net Bookings - To do) MTD]) - 'Table'[Total Charter Budget MTD]
)

Result = 
CALCULATE(
    [Cumulative Revenue Difference],
    FILTER(
        'Table',
        SELECTEDVALUE('Table'[Day]) <= 4 
    )
)

Final output

The above example is just hardcoded, you can dynamically adjust your filtering scope according to your slicer.

Best regards,
Albert He

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

[sabrinekalbousi]

happy new year and thanks for your reply:)

Unfortunately, this solution cannot work for me; 

these mentioned are measures not columns

[Revenue (Net Bookings - Done) MTD] 
[Revenue (Net Bookings - To do) MTD]
[Total Charter Budget MTD]

these measures based on calendar[date]

the desired cumulative measure should calculate the cumulative day after day based on the selected

filter[date]. for instance, if I select December2024 in the filter[date] slicer, I need to get a cumulative day by day from the 1/12/2024 until the 31/12/2024.  I hope you understand me.

[v-heq-msft]

Hi @sabrinekalbousi ,
You can try to use DATESBETWEEN function to explicitly specify a date range.

Total Charter Net Revenue Gap running total in DateId MTD =
VAR _lastDate = IF(TODAY() < MAX('CalendarFilter'[DateId]), TODAY(), MAX('CalendarFilter'[DateId]))
VAR _firstDate = DATE(YEAR(_lastDate), MONTH(_lastDate), 1)
RETURN
    CALCULATE(
        ([Revenue (Net Bookings - Done) MTD] + [Revenue (Net Bookings - To do) MTD]) - [Total Charter Budget MTD],
        DATESBETWEEN(
            'CalendarStart2020V'[DateId],
            _firstDate,
            _lastDate
        )
    )

If the above modifications still can't solve your problem, you can provide the full example data, preferably a pbix file, so we can help you more accurately. Please hide sensitive information in advance.

Best regards,
Albert He

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