Very Complex Accumulated Values with Time

[brankocareer]

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!

[Anonymous · Accepted solution]

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.

[Greg_Deckler]

@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.