Forum Discussion
Problem getting the right values for issues registered and closed
Hi there,
I have a question:
I am preparing an issue report where I have to visualize the following:
1. Date filter (let's consider the month of April 2020)
2. Calculate issues registered (Measure = SUM or Count issues based on register date)
3. Calculate issues closed (Measure = Sum or Count issues based on closing date)
4. Calculate issues before April = here I have problem: I am using the following measure:
RETURN
Result = Issues Registered - Issues Closed
4. Calculate issues end period = Issues begin period (Before April) + Issues registered - Issues Closed
What I try to do is:
I want to know how many issues were still not closed before SELECTEDMONTH April
+
Issues registered in April
+
Issues Closed in April
+
Final Status = How many issues are not closed
I hope someone can help me. The scenario in Excel What I want to achieve in Power BI Desktop.
Thank you.
5 Replies
Anonymous , If you want to filter on two date. You can join both of them with the same date. And use userelation in the formula to choose a date
Refer example of this blog
Thank you for your reply.
I am aware of your comment but my request is quite different.
I need to calculate all the issues before SELECTED Month APRIL.
Please see carefully my case.
Hi Anonymous ,
If you want to get the yellow expected result just consider April, why not calculate it directly.
Sample data:
Issue registered = SUM('Table'[Registered])Issue closed = SUM('Table'[Closed])Issue Registered April = CALCULATE ( [Issue registered], MONTH ( 'Table'[Date] ) = 4 )Issue Closed April = CALCULATE ( [Issue closed], MONTH ( 'Table'[Date] ) = 4 )Issue Registered < April = CALCULATE ( [Issue registered], MONTH ( 'Table'[Date] ) < 4 )Issue Closed < April = CALCULATE ( [Issue closed], MONTH ( 'Table'[Date] ) < 4 )Begin period = [Issue Registered < April] -[Issue Closed < April]End period = [Begin period] + [Issue Registered April] - [Issue Closed April]Result:
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your explanation
I would like that " Before period" to be dynamic. means if I change the date range to MARCH then i should calculate all before March.
Hi Anonymous ,
Try like this measure to calculate period month total by using the slicer:
Issue Registered < selected month = VAR _month = SELECTEDVALUE ( 'Table'[Date].[MonthNo] ) RETURN CALCULATE ( [Issue registered], FILTER ( ALL ( 'Table' ), MONTH ( 'Table'[Date] ) < _month ) )If you select March, it will calculate total registered based on Jan and Feb:
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
