Forum Discussion

brankocareer's avatar
brankocareer
Icon for Helper I rankHelper I
2 years ago
Solved

Very Complex Accumulated Values with Time

Hi all,

I have a table including columns [Position ID],[Modified Date],[FTE Count]. What I need is the accumulated distinct count of  [Position ID] with time. The logic is as follow:

[Position ID] A, have 6 [FTE Count] records  (0,0,0,0,0,1) , A is counted since the first 1.
[Position ID] B have 6 records of FTE, (0,1,0,0,0,0) , B is counted at the time of first 1, and since following 0, B is not counted.
After all, if at a time point [FTE Count]=0,the [Position ID] will be not counted; if at a time point [FTE Count]=1, the [Position ID] will be counted.

I spent a lot of time to write the measure, but it is still not right.

My incorrect meassure:
Accumulated Position Test =
VAR CurrentDate = MAX('Energy_FTE_History'[Modified Date])
RETURN
    CALCULATE(
        DISTINCTCOUNT('Energy_FTE_History'[Position ID]),
        FILTER(
            ALL('Energy_FTE_History'),
            'Energy_FTE_History'[Modified Date] <= CurrentDate &&
            'Energy_FTE_History'[FTE Count] = 1
        )
    )

 

I really need help!

 

Thank you in advance!

"Need Help"
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi brankocareer 

     

    I checked your code and the problem may occur in the judgment of cumulative conditions. The following is the solution I provided:

     

    Here's some dummy data

     

    "Energy_FTE_History"

     

    The modified code is as follows.

     

    Accumulated Position Test = 
VAR CurrentID = MAX('Energy_FTE_History'[Position ID])
RETURN
    CALCULATE(
        DISTINCTCOUNT('Energy_FTE_History'[Position ID]),
        FILTER(
            ALL('Energy_FTE_History'),
            'Energy_FTE_History'[Position ID] <= CurrentID &&
            'Energy_FTE_History'[FTE Count] = 1
        )
    )

     

    Filter out FTE Count of 0.

     

     

    Here is the result.

     

    Regards,

    Nono Chen

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

     

     

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    brankocareer Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi brankocareer 

     

    I checked your code and the problem may occur in the judgment of cumulative conditions. The following is the solution I provided:

     

    Here's some dummy data

     

    "Energy_FTE_History"

     

    The modified code is as follows.

     

    Accumulated Position Test = 
VAR CurrentID = MAX('Energy_FTE_History'[Position ID])
RETURN
    CALCULATE(
        DISTINCTCOUNT('Energy_FTE_History'[Position ID]),
        FILTER(
            ALL('Energy_FTE_History'),
            'Energy_FTE_History'[Position ID] <= CurrentID &&
            'Energy_FTE_History'[FTE Count] = 1
        )
    )

     

    Filter out FTE Count of 0.

     

     

    Here is the result.

     

    Regards,

    Nono Chen

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