Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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.
@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:
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
@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 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))
@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:
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.