Cumulative Turnover Rate Wrong Sequence

[Evita_2023]

Hi,

I have 2 tables in Power BI Desktop - Employees & Date Table. 

I have calculated measure for Turnover Rate saved in Date table. 

I want to calculate Cumulative Turnover rate for selected period so that I can create a line visual where it displays cumulative Turnover rate. 
Please see below desired result:

I have an issue:
1. When I don't set up the filter, it calculates correctly, but from all data, not from selected time period. Please see below code and result (I am adding TurnoverRate in results for comparisson):

Cumulative Turnover Rate =

CALCULATE (

    SUMX ( 'Date', [TurnoverRate] ),

    FILTER ( ALL ( 'Date' ), [Date] <= MAX ( [Date] ) )

)

i have used this formula from previuous posts. 

2. When I specify filter, it gives me incorrect order. Please see code and results:

Cumulative Turnover Rate =

VAR SelectedMonths =

    FILTER(

        ALL('Date'),

        'Date'[Date] >= MIN('Date'[Date])

    )

RETURN

    SUMX(

        SelectedMonths,

        [TurnoverRate]

    )

I need to turn it around. Below is desired result:
April: 0.93
May: 1.44 (0.93 + 0.51)
June: 2.83 (0.93 + 0.51 + 1.40)
July: 4.02 (0.93 + 0.51 + 1.40 + 1.19)
August: 4.31 (0.93 + 0.51 + 1.40 + 1.19 + 0.30)

3. When I specify Date range then there is no cumulative rate by selected month. Please see below code and result:

Cumulative Turnover Rate =

VAR SelectedMonths =

    FILTER(

        ALL('Date'),

       'Date'[Date] >= MIN('Date'[Date]) && 'Date'[Date] <= MAX('Date'[Date])

    )

RETURN

    SUMX(

        SelectedMonths,

        [TurnoverRate]

    )
Can you please be so kind and advise? 
There must be simple tweak, but even ChatGP couldn't help. 

Thank you

[Anonymous]

Hi @Evita_2023 ,

Please try:

Cumulative Turnover Rate 2 = 
VAR __lastdate =
    MAX ( 'Date'[Date] )
VAR __firstdate =
    MIN ( 'Date'[Date] )
VAR __lastdatewithvalue =
    CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR __result =
    IF (
        __firstdate <= __lastdatewithvalue,
        CALCULATE (
            CALCULATE (
                SUMX ( 'Date', [TurnoverRate] ),
                FILTER (
                    ALL ( 'Date' ),
                    'Date'[Date] >= MIN ( 'Date'[Date] )
                        && 'Date'[Date] <= MAX ( 'Date'[Date] )
                )
            ),
            'Date'[Date] <= __lastdatewithvalue,
            VALUES ( 'Date'[Date] )
        )
    )
RETURN
    __result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Evita_2023]

Hi Gao,
I hacve tried, but I still get the same result.
In your snapshot of table, you can see that turnover rate doesn't grow by months.

Please see below desired result:

April: 0.93
May: 1.44 (0.93 + 0.51)
June: 2.83 (0.93 + 0.51 + 1.40)
July: 4.02 (0.93 + 0.51 + 1.40 + 1.19)
August: 4.31 (0.93 + 0.51 + 1.40 + 1.19 + 0.30)

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.