Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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])))
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
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.
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.
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])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |