Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Daily Count By Category

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 c...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago
    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 
     
v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago
        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