Forum Discussion
Access previous row dynamically based on date filter
I am trying to get Previous row value based on selected filter for the date.
The report can be filtered on month/Quarter/Year. This makes it dynamic.
The date column in some older cases has null value as well. I tried using RankX but was not successful.
Summarizing it on the counts of Anomalies open in x year and carryforwarded to next year.
| Anomaly Number | Status | Open Date | Closed Date |
| 1 | anom_completed | 6/19/2015 12:33 | 6/23/2015 9:21 |
| 2 | anom_completed | 6/19/2015 14:25 | 10/7/2015 10:32 |
| 3 | anom_completed | 6/19/2015 14:30 | 10/7/2015 10:32 |
| 4 | anom_completed | 6/19/2015 15:58 | 9/21/2016 9:50 |
| 5 | anom_completed | 6/19/2015 16:05 | 3/16/2016 14:01 |
| 6 | anom_completed | 6/20/2015 9:13 | 12/3/2015 10:39 |
| 7 | anom_completed | 6/20/2015 9:41 | 12/3/2017 10:41 |
| 8 | anom_completed | 6/22/2017 21:41 | 2/10/2018 10:04 |
| 9 | anom_completed | 6/23/2015 9:22 | 3/16/2016 14:01 |
| 10 | anom_completed | 4/10/2019 4:27 | 6/25/2019 12:02 |
| 11 | anom_completed | 4/10/2019 5:03 | 7/2/2019 4:46 |
| 12 | anom_completed | 4/10/2019 9:17 | 2/20/2020 14:24 |
| 13 | anom_completed | 4/10/2019 10:53 | 8/9/2019 14:08 |
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.
10 Replies
Thanks,
Yes, This would have been a viable solution, but the only problem is the date filter where a person can select a quarter / Month or Year. If the case was only Year, this solution would have worked.
but on dashboard, if someone selects a quarter or a month, this might not work.
Thanks Nate
Table.AddColumn(Table, "IsCarryOver", each if Date.Year([Close Date]) > Date.Year([Open Date]) then 1 else 0, type number)
per the formula "isCarryOver" value would reflect 1, if it was created in 2016 and closed in 2017. The same thing woudnt work if the filter on the table is for a Q1 of 2016. Which mean I need isCarryover value to be dynamic.
Hope I am able to provide you with my problem statement.
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 _ JingYes, 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.
