How to translate daily revenue total into yearly cumulative total?

[DianaT]

Hi all,

I'm trying to do a revenue forcast using a dataset that contains the following columns: 

Client ID   Program enroll      Start date         End date      Instalment amt    Instalment frequency    Daily revenue (calculated based on instalment amt and frequency

And a date table generated from Calendarauto

And below is a measure I used to calculate the daily total from 2021 onward

Daily revenue total =

CALCULATE(SUM('AF DESIGNATION FORECAST'[Daily revenue]),

FILTER(VALUES('AF DESIGNATION FORECAST'[Start date]),'AF DESIGNATION FORECAST'[Start date] <= MAX ('Date'[Date])),

FILTER(VALUES('AF DESIGNATION FORECAST'[ End date]),'AF DESIGNATION FORECAST'[End date] >= MIN ('Date'[Date])))

And I manage to get results like this

However, as I try to get the culumulative total of each year (say 2021), this measure doesn't seem to work with it... What has gone wrong?

Any help will be greatly appreciated. Thank you.

Diana

[v-kkf-msft · Accepted solution]

Hi @DianaT ,

Try the following formula:

Measure = 
CALCULATE(
    SUM('AF DESIGNATION FORECAST'[Daily revenue]),
    FILTER(
        'AF DESIGNATION FORECAST',
        'AF DESIGNATION FORECAST'[End date] < MAX('Date'[Date])
        && YEAR('AF DESIGNATION FORECAST'[End date]) = MIN('Date'[Year])
    )
) + [Daily revenue total]

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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