Forum Discussion

Evita_2023's avatar
Evita_2023
Regular Visitor
3 years ago

Cumulative Turnover Rate Wrong Sequence

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
Need Help

3 Replies

  • foodd's avatar
    foodd
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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-Forum/ba-p/963216

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Evita_2023
      Regular Visitor
      3 years ago

      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)