Forum Discussion

LouisSh's avatar
LouisSh
Frequent Visitor
4 years ago

Count Between Two Dates

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

 

Need Help

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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's avatar
      LouisSh
      Frequent Visitor
      4 years ago

      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)