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
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 - _closeCount
Currently 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.
i got the numerator and denominator worked out based on the inputs you provided. The solution works.
Though, when these calculations are done, they some how stop working with filters. I had a Project Name category for each anomaly. Now the graphs are not filtering based on project names.