Access previous row dynamically based on date filter

[k_rahul_g]

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

[v-jingzhang]

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

[k_rahul_g]

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.  

[v-jingzhang · Accepted solution]

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.

[k_rahul_g]

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. 

[v-jingzhang]

Hi @k_rahul_g, what should be the numerator and denominator in the % closure? You need to create a new measure for it. 

[k_rahul_g]

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.

[watkinnc]

Well, you'd still be summing each filtered row, so you should be good, whether you are filtering, grouping, whatever, you are still just summing 1s. This is a column in Power Query, so filtering would be very predictable.

--Nate

[k_rahul_g]

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. 

[watkinnc]

You could add a custom column like this:

Table.AddColumn(Table, "IsCarryOver", each if Date.Year([Close Date]) > Date.Year([Open Date]) then 1 else 0, type number)

Now you can just sum the 1's.

--Nate

[k_rahul_g]

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.