Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Cumulative Count by Event Date

I have a table of objects that has multiple date columns for different event milestones (provided from a gantt schedule output). I need a measure to calculate the cumulative count of events by event date over a calendar period 

 

ProjectMilestone 1 DateMilestone 2 Date

Project A

1/5/20202/1/2020
Project B2/5/20202/20/2020

 

DateCumulative M1 CountCumulative M2 Count
1/202010
2/202022
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

    I build an Event table like yours and a Date table to have a test.

    Event Table:

    Date Table:

    Date = SELECTCOLUMNS (

        Event,

        "Year", YEAR ( Event[Milestone 1 Date] ),

        "Month", MONTH ( Event[Milestone 1 Date] )

    )

    Result:

    Then we build measure to count M1 and M2 by date.

    M1 Count =

VAR _CountM1 = COUNTROWS(FILTER(ALL(Event),Event[Milestone 1 Date].[MonthNo]<=MAX('Date'[Month])&&Event[Milestone 1 Date].[Year]=MAX('Date'[Year])))

RETURN

IF(_CountM1=0,0,_CountM1)
    M2 Count =

VAR _CountM2 = COUNTROWS(FILTER(ALL(Event),Event[Milestone 2 Date].[MonthNo]<=MAX('Date'[Month])&&Event[Milestone 2 Date].[Year]=MAX('Date'[Year])))

RETURN

IF(_CountM2=0,0,_CountM2)

    Result is as below:

    You can download the pbix file from this link: Cumulative Count by Event Date

     

    Best Regards,

    Rico Zhou

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous Thanks for the help. This is almost working but the ALL() argument is throwing my intended calculation off. I have a few dimension tables related to the EVENT table that I need to slice and filter the measure. It seems this is preventing that. 

       

      I tried removing the ALL() component and just used the 'EVENT' table as the first FILTER() argument, but that seems to return an instantaneous count and not a cumulative count. I am assuming because the DATE field is providing filter context to the table. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous I need to provide some additional modeling details. 

         

        The measure is usually tracked on a daily basis, so I need this to work on a daily date granularity. 

         

        Also, 

         

        My Model is setup with 3 tables:

         

        1. Calendar Table
        2. Activity Table (Unpivoted)
          1. {Date Column, ID, EventType, EventStart, EventEnd}
        3. Pivoted Event Table
          1. {ID, Event1.Start, Event1.End, ..... , EventN.Start, EventN.End}

        Relationship Wise

        1. Calendar 1:* Activity Table (Date:Date)

        2. Pivoted Event Table 1:* Activity Table (ID:ID)