How to calculate amount level of running implementation projects over time

[Anonymous]

Hello,

I'm currently trying to calculate the amount level of Implementations in progress to visualize the increase/decrease of workload over time.

At first I thought it was as simple as 'total amount - amount completed = running amount'.

However 2 date fields play their part (DateCreate and DateCompleted).

So it appears it's not that simple. 

I have 1 fact table with the implementations (and their unique ID's). In this table I have 2 date fields (DateCreated and DateCompleted) as well as a column 'Status', 0 = open, 1 = complete.

I have a Calendar dimension with an active relationship betweent Calendar.Date and FACT_Implemenations.DateCreate.

Also an inactive relationship between Calendar.Date and FACT_Implemenations.DateCompleted.

I tried the following measures;

measure1: 

Running Impl =

VAR RunningPlacedTotal =

CALCULATE (

DISTINCTCOUNT( FACT_Implemenations[ImplementationId] ),

FILTER (

ALL ( 'Calendar'[Date] ),

ISONORAFTER ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), DESC )

)

)

VAR RunningCloseTotal =

CALCULATE (

DISTINCTCOUNT( FACT_Checks[ImplementationId]),

USERELATIONSHIP ( 'Calendar'[Date], FACT_Implementations[DateCompleted] ),

FILTER (

ALL ( 'Calendar'[Date] ),

ISONORAFTER ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), DESC )

)

)

RETURN RunningPlacedTotal - RunningCloseTotal

----

measure2:

Stock on Hand Impl =

var _currdate = MAX('Calendar'[Date])

return

CALCULATE(

    DISTINCTCOUNT('FACT_Implementations'[Implementations_Id]),

USERELATIONSHIP('Calendar'[Date], FACT_Implementations[DateCompleted]),

    FILTER(

        ALLSELECTED('Calendar'[Date]),

        ISONORAFTER('Calendar'[Date], _currdate, DESC)

    )

)

So far I'm not getting the desired output and I'm kinda stuck.

Hope you guys/girls can help me.  

Thnx in advance

[parry2k]

@Anonymous Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490