Forum Discussion
Access previous row dynamically based on date filter
Hi k_rahul_g
I don't think we need to get previous row results. You can add a calendar table to the model and use the following measure.
Carryforward to Next Year = VAR _selectMaxDate = MAX('Calendar'[Date]) VAR _openCount = COUNTX(FILTER(ALL('Table'),'Table'[Open Date]<=_selectMaxDate),'Table'[Anomaly Number]) VAR _closeCount = COUNTX(FILTER(ALL('Table'),'Table'[Closed Date]<>BLANK()&&'Table'[Closed Date]<=_selectMaxDate),'Table'[Anomaly Number]) RETURN _openCount - _closeCountCurrently I name it as "Carryforward to Next Year", but it also works if you select a month or quarter. It calculates the carryforward result at the end of the last date of your selected dates period. And I assume that there may be null/blank values in Closed Date column if some anomalies are still in open state now.
Let me know if you have any questions.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi k_rahul_g
If you want a dynamic result, you need to create a measure to achieve the result rather than add a column.
When you filter by month or quarter, do you mean to filter the Anomalies whose open dates are in the selected month/quarter and to count the number of those whose closed dates are in next few years?
Best Regards,
Community Support Team _ Jing
Yes, I am looking for a measure so that it can work according to the filter.
Let me eloborate the problem again.
Number of anomalies open in 2015 - 2000
Number of anomalies closed in 2015 - 1500
Carryforward to 2016 - (2000 - 1500) = 500
Number of anomalies Open in 2016 - 3000 ( Add carryforward ) - total is 3500
Number of anomalies closed in 2016 - 2800
Carryforward to 2017 - (3500 - 2800) = 700
This tells the burnout efficiency of the team.
in data i have OPEN DATE, CLOSED DATE, and other filter fields.
I am not able to achieve this in any way. Tried RANKX , Index etc to get previous row results but I am new to DAX so need help.
Hi k_rahul_g
I don't think we need to get previous row results. You can add a calendar table to the model and use the following measure.
Carryforward to Next Year = VAR _selectMaxDate = MAX('Calendar'[Date]) VAR _openCount = COUNTX(FILTER(ALL('Table'),'Table'[Open Date]<=_selectMaxDate),'Table'[Anomaly Number]) VAR _closeCount = COUNTX(FILTER(ALL('Table'),'Table'[Closed Date]<>BLANK()&&'Table'[Closed Date]<=_selectMaxDate),'Table'[Anomaly Number]) RETURN _openCount - _closeCountCurrently I name it as "Carryforward to Next Year", but it also works if you select a month or quarter. It calculates the carryforward result at the end of the last date of your selected dates period. And I assume that there may be null/blank values in Closed Date column if some anomalies are still in open state now.
Let me know if you have any questions.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.It does solve part of the problem and calculates the carry over
.
Problem for me is to use the carry over in the next (filtered calendar range) i.e.
If carry forward for 2016 is 6 and total added in 2017 is 8 - the total is 14 (to be closed in 2017) now if the team closed 10 out of those 14 than the next carryforward is 4. I have to calculate the % closure in the selected period.
Hi k_rahul_g, what should be the numerator and denominator in the % closure? You need to create a new measure for it.