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 ...
Need Help
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)