Count Between Two Dates

[LouisSh]

Hi,

I have reviewed other solutions but none seem to do what i want it to do. I have a list of agents in a DimParty Table which has a start date column and an expected end date column. I have a DimDate table where the active relationship is between the start date of DimParty. I want to be able to plot a graph with shows on a gievn day what the count of agents, and obviously stop counting the rows after the end date. I'm not sure if the issue is with the none active relations with the end date. Here are some solutions i have tried;

new v3 = calculate (
[Agent Count Measure],
DATESBETWEEN(DimDate[Date], min(DimParty[StartDate]), max(DimParty[EndDate])))

This example uses a date supporting table rather than the dimdate. This kind of works however the total for current day is way off what the actual count is;

New v1 = SUMX (

Dates,

CALCULATE (

distinctcount(DimParty[PartyId]),

FILTER (

'DimParty',

[Date] >= 'DimParty'[StartDate]

&& [Date] <= DimParty[EndDate]

)

)

)

This is the output of the second query. The trend looks right, however the volumes are not. Today, there should be a could of 27 however it is showing as 5

This is the example from the second (example 2) DAX query. The graph looks right however the total for current day should be 27 as that is the current count however it is showing 5. But the graph itself is what i would expect to see in terms of the trend

 

[mahoneypat]

Try adding in an ALL and I recommend modifying your FILTER too. Replace Date with Dates and Agents with DimParty.

AgentCount =
SUMX(
    'Date',
    CALCULATE(
        COUNT( Agents[AgentID] ),
        ALL( Agents ),
        FILTER(
            ALL( Agents[StartDate], Agents[EndDate] ),
            Agents[StartDate] <= 'Date'[Date]
                && Agents[EndDate] >= 'Date'[Date]
        )
    )
)

Pat

[LouisSh]

Thanks Pat, I've managed to pick apart on your help and get to where i need to be 🙂

Just to note, I didnt help the situation by not referring to agent table as DimParty, so i have amended the text above (to clarify, there is no DimAgent table, its DimParty which holds the agent details, i just tripped over myself)

[amitchandak]

@LouisSh , is Agent a SCD ?

refer

https://www.zartis.com/scd-implementation-with-temporal-tables-in-power-bi/

from guyinacube

https://www.youtube.com/watch?v=tKeaQpWynzg