Cumulative count of records with look-back to one year

[Anonymous]

Hello everyone,

   I'm working with a dataset for registering risks which registers the following relevant columns: 
State: "Active" or "Closed".

DateIdentified: date type column to show when the risk was registered.
ClosedOn: date type column to show when the risk was mitigated/closed.

   I'm looking to create a bar chart that has only a look back to one year from today and divides the risks into the current quarter and then the previous 3 quarters (eg. if today we're in Q3 2019 we look back at Q2 2019, Q1 2019 and Q4 2018 only). This is obviously dependant on the current date. The data has to show for each quarter "New", "Closed" and "Active" risks.

A "New" risk is a risk that has the DateIdentified in the same quarter as the quarter we are referring to. A "Closed" risk is a risk that has only ClosedOn date the same quarter as the quarter we are referring to (the risks that have a ClosedOn date also show for the State column as "Closed", otherwise there is no date for the ClosedOn column. An "Active" risk is a risk that has a date identified before the quarter we are referring to, but is still not closed (i.e. if there is a DateIdentified for the risk, it is in a future quarter).

So for example, now we are in Quarter 3 of 2019. If a risk has DateIdentified  March 15, 2019 and doesn't have a ClosedOn date, it will show for Q1 2019 as "New" , for Q2 2019 as "Active" and for this quarter also as "Active".

If we have a risk with DateIdentified December 27, 2018 and ClosedOn June 15,2019 , for Q4 2018 it shows as "New",  for Q1 2019 it shows as "Active", for Q2 2019 it shows as "Closed" and for the current quarter it will not show anymore.
If a risk has DateIdentified on April 2nd 2019 and ClosedOn May 25th 2019, it should ideally show only Q2 2019 as "New" once then as "Closed" once (if not possible should show as "Closed" only once).

If a risk was closed before past 4 quarters it shouldn't show up at all in the count.

So, ideally, a bar graph will show something like:

Q4 2018:

Active:100

New:5

Closed:10

Q1 2019:

Active:95

New:15

Closed:18

Q2 2019:

Active:92

New:7

Closed:11

Q3 2019:

Active:88

New:4

Closed:12

My idea was to create 4 different columns categorizing each risk relative to time as either "New" ,"Active" or "Closed" and anything that doesn't fit that criteria is "Inactive" and is filtered out ( risks that were closed before the last 4 quarters, risks that haven't been identified relative to the quarter we are looking at). 

For the quarter before the current quarter my DAX formula looks like this:

Active_Quarter_Minus_1 = IF(

OR(

AND('HPC Risk Register'[State] = "Active",'HPC Risk Register'[DateIdentified].[Date] < STARTOFQUARTER(DATEADD('HPC Risk Register'[Today Date].[Date],-1,QUARTER))),

AND('HPC Risk Register'[Closedon].[Date]>= STARTOFQUARTER(DATEADD('HPC Risk Register'[Today Date].[Date],0,QUARTER)),'HPC Risk Register'[DateIdentified].[Date] < STARTOFQUARTER(DATEADD('HPC Risk Register'[Today Date].[Date],-1,QUARTER)))),"Active",

IF(

AND(

AND('HPC Risk Register'[Closedon].[Date]>=STARTOFQUARTER(DATEADD('HPC Risk Register'[Today Date].[Date],-1,QUARTER)),'HPC Risk Register'[Closedon].[Date]<=ENDOFQUARTER(DATEADD('HPC Risk Register'[Today Date].[Date],-1,QUARTER))),'HPC Risk Register'[State]="Closed"),"Closed",

IF(

AND(

AND('HPC Risk Register'[DateIdentified].[Date] >= STARTOFQUARTER(DATEADD('HPC Risk Register'[Today Date].[Date],-1,QUARTER)),'HPC Risk Register'[DateIdentified].[Date] <= ENDOFQUARTER(DATEADD('HPC Risk Register'[Today Date].[Date],-1,QUARTER))),

'HPC Risk Register'[Closedon].[Date] <= ENDOFQUARTER(DATEADD('HPC Risk Register'[Today Date].[Date],0,QUARTER))),"New","Inactive")))

I'm not sure what better way it to do this. I also think I found out that when I look back to Q4 2018 there is a bug with the DATEADD() function going back to one year. Another issue is risks that are both identified and closed in the same quarter. Any suggestions to make this into one visual would be great, now I'm making multiple bar charts and aligning them beside each other.

Thank you,

Bogdan

[amitchandak]

I am not able to get a summary of the issues. Can you please summarize the issues and solution needed for that. 

Few pointers based  on what I got

1. In powerbi, prefer to use a timetable joined to fact/table to do time calculation 

2. If needed, you can join two dates one in active mode and one in inactive mode

3. Use these joins in CALCULATE by having userelationparameter. 

[Anonymous]

@amitchandak The issues summarized are:
My count with the formula I provided doesn't show as the example in my original post.

This is partly because risks that are have DateIdentified and ClosedOn dates in the same quarter are not being counted as "New" and as "Closed" separately.

Another issue is that I cannot find a formula that works looking back the to the last quarter. I created a column for current quarter, the one before, two quarters ago, but it won't work looking back the 3rd quarter.
Also I am not sure how I can present this in one bar chart.

Here is a visual of the idea, it is missing the new risks in here, but you get the idea:

[amitchandak]

Please confirm

New - Open in this QTR, but not closed.

Active - Open in QTR Before and Closed in QTR after

Close - Open in QTR Before and closed in this QTR

[Anonymous]

@amitchandak  
New - created in the QTR we are referring to, but not closed in that QTR.

Active - still open the QTR we are referring to, but it was created in a QTR before this one.
Closed - open in QTRs before or this QTR and closed this QTR.

EDIT: To explain the bug, I used the DATEADD function to get last quarter date for my formula. The issue is that right now we're in Q3 and the earliest QTR is Q4 2018. I used 

Check = DATEADD('HPC Risk Register'[Today Date].[Date],-3,QUARTER)

to get the Q4 2018 date, but this function doesn't work when it has to go back a year so now I'm stuck.

[amitchandak]

Check This

Sales Before QTR Recived this Qtr =
CALCULATE(SUMx(FILTER(sales,and(Sales[Order_Date]>= STARTOFQUARTER(DATEADD(STARTOFQUARTER(OrderTime[Order Date]),-1,DAY)) && Sales[Order_Date]<= DATEADD(STARTOFQUARTER(OrderTime[Order Date]),-1,DAY),
Sales[Requested_Date]>= STARTOFQUARTER(OrderTime[Order Date]) && Sales[Requested_Date]<= STARTOFQUARTER(OrderTime[Order Date])
)),Sales[Sales]),CROSSFILTER(Sales[Order_Date],OrderTime[Order Date],None))

[Anonymous]

@amitchandak 
The part:

STARTOFQUARTER(DATEADD(STARTOFQUARTER(OrderTime[Order Date]),-1,DAY))

 doesn't work for me when I modify the formula to look back to Q4 2018 because it doesn't return any value when going back a year. Here is what I used to look back to Q4 2018: 

Check = DATEADD(STARTOFQUARTER('HPC Risk Register'[Today Date].[Date]),-3,QUARTER)

This should return 10/01/2018 12:00:00 AM, but it returns a blank.

Doing it for Q1 2019 with the formula below:

Check = DATEADD(STARTOFQUARTER('HPC Risk Register'[Today Date].[Date]),-2,QUARTER)

It returns 1/1/2019 12:00:00 AM which is what I need. I haven't found a better way to do it which is why I'm stuck.

This formula will stop working when it will be for example Q1 2020 and the formula will look back to Q4 2019, Q3 2019 and Q2 2019 because it will need to go back to the previous year.

[amitchandak]

I think once we choose any time filter, like Q4-2018. The Formula does get that filter. That same reason I put the crossfilter into the calculate. Also, I made the time table marked as a Date table in Power BI. When you want to look back, use all(Dates) to make sure the filter selected is not applied. Hope this will help.