Creating dynamic groupings based on dates

[Clint]

Hello,

I have a report that pulls data from Project Server On-line. I have created a custom field in Project Server called "FeatureTargetComoplete".  One of the measures we report is a quarterly report of features that hit/missed their FC date each quarter.  What I've done to facilitate showing this correctly in a line chart is create a group for the FeatureTargetComplete field and manually added all of the returned dates into groups based on the quarter they fall in.... We use custom quarter names such as DQ18 (Oct18-Dec18)MQ19 (Jan19-Mar19) etc.....  This works however, what I'd like to do is be able to have new FC dates that fall in these quarters automatically get added to the right quarter.  Otherwise, FC dates for new projects won't be added to these groups unless I add them manually.  I'm pretty sure there are many better ways to skin this cat but I'm not sure what they might be.  any insights are appreciated.

[v-juanli-msft]

Hi @Clint

It's not clear for me.

Could you show some data examples?

Best Regards

Maggie

[Clint]

Hi Maggie,

Sure.  In any given quarter we'll have a range of projects that have Feature Complete dates in that quarter.

The FC Baseline field is actually a column called Featuretargetcomplete in the Projects table from Project Server.  I created a group on that field and grouped the dates in groups called 1.DQ18, 2.MQ19, 3.JQ19,  This allowed me to create a chart that looks like this:

The problem is, when I grouped the dates into quarters, I could only group dates for the current projects.  So, if/when new projects come in (or dates change), the groups will not adjust dynamically to reflect these changes.  How do I create groups that automatically group dates into the right quarters?

[v-juanli-msft · Accepted solution]

Hi @Clint 

I would suggest you to create calcuated columns.

First go to Edit queries->Add column->add year, quarter, day

Then close&apply, create a calcuated column, then add this column as the Axis value instead.

Column =
VAR NEW_QUARTER =
    SWITCH ( [Quarter], 1, "2.MQ", 2, "3.JQ", 3, "4.SQ", 4, "1.DQ" )
VAR YEAR =
    RIGHT ( [Year], 2 )
RETURN
    CONCATENATE ( NEW_QUARTER, YEAR )

Best Regards

Maggie

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

[Clint]

Thanks Maggie!! I will give this a shot.