Daily Count By Category

[Anonymous]

I am trying to track the progression of some projects and the different cycles of developemnt they go through. For example I would like to see in a line chart with a line for each cycle and see the correlation or movement between these cycles. I am having a hard time coming up with a running count as there are duplicates within the table.

I am trying to create a column 'RunningCount' which gives the number of projects for a specific phase at the point in time when that project started, or milestone was reached. The challenge is that I only want to include the most recent phase of the project and that depends on time. 

This is what I have so far

RunningCount = 
    VAR MeasureDate = 'test measure'[created_datetime]
    VAR Engagement = 'test measure'[Engagement]
    VAR Phase = 'test measure'[Phase]
    VAR Project = 'test measure'[project_id]
    VAR varibMaxDate = IF(MeasureDate = CALCULATE(MAX('test measure'[created_datetime]), FILTER(ALL('test measure'), 'test measure'[project_id] = EARLIER('test measure'[project_id]))),1,0)

    RETURN
        COUNTROWS(
            FILTER( 
                ALL('test measure'),
                'test measure'[created_datetime] <= MeasureDate
                && 'test measure'[Engagement] = Engagement
                && 'test measure'[Phase] = Phase
                && 'test measure'[Rank] >= EARLIER('test measure'[Rank])
                && varibMaxDate = 1
            )
        )

The `varibMaxDate` isn't really dynamic for the row but for the table as a whole.

This is what the table looks like

Thought on how to dynamically calculate this boolean?

Any ideas on how I could accomplish this other than they way I am attempting to now?

[v-lid-msft]

Hi @Anonymous ,

As the DAX you shared, the “varibMaxDate” is used to mark a latest row of a project, and it must filter all the table and find the same project to compare date. The variable works fine as following

But if you want to make this infused by a filter such as time slicer, you may need to use a measure to count because calculated columns are computed during the database processing and then stored in the model.

If it doesn't meet your requirement, kindly share your excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

[Anonymous]

Evening @v-lid-msft,

I am not sure I am following, what did you change? 

Sorry wrote up this long response and it got deleted....so here we go again. I want the chart to reflect the count of each phase for each month even if some change while the other is stagnant. 

It seems like a need to populate a dimension table with each month then created a calculated column but I can not seem to do it. 

I have attached a file with a doctored version and an example of what I am looking for. 

[v-lid-msft]

Hi @Anonymous ,

I am sorry that I still can not understand your method of counting, in the expamle you provide, there are 3 types in 2018-10, 

but the test table, begin with 2018-10, just have only one type and count as 12.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Evening @v-lid-msft,

Sorry for the confusion I will try to be a little more descriptive in my next post. The three types in the example map to the `phase text` which roll up into a high types which I call `Phase` or `business phase` in the new file, which is a calculated column.  

So here are the steps I went through to find the solution. I don't think this is the only way, or correct way but it works.

1. I added a month column to the test table that classifies all dates in a month to the first day of the month.

Month = date(Year('test measure'[created_datetime]) ,month('test measure'[created_datetime]),1)

2. I created a table with all the first days of the months between min and max dates.

Table 6 = 

var dateTable2 =
    GENERATE ( CALENDAR(
    DATE ( YEAR ( MIN ( 'test measure'[created_datetime]) ), 1, 1 ),
    DATE ( YEAR ( MAX ( 'test measure'[created_datetime])  ), 12, 1 )),
    VAR currentDay = [Date]
    VAR month = MONTH ( currentDay )
    VAR year = YEAR ( currentDay )
    RETURN ROW ( 
    "MMYYDD", DATE(year,month,01) )
    )

return groupby(dateTable2,[MMYYDD])

3. Then I cross joined all project_id's with these dates to create another table.

Table 7 = filter(crossjoin('Table 6',DISTINCT(SELECTCOLUMNS('test measure',"ID",'test measure'[project_id]))),[ID]<>"#N/A")

4,Once I had this table I looked up the phase for a specific project where the max date for that project was less than or equal to the current row date.

Solution = ADDCOLUMNS('Table 7',"PHASE",LOOKUPVALUE('test measure'[phase_text],'test measure'[project_id],[ID],'test measure'[created_datetime],maxx(filter('test measure','test measure'[project_id]='Table 7'[ID] &&'test measure'[Month]<=earlier('Table 7'[MMYYDD])),[created_datetime]))) 

 

Here is the file