Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
k_rahul_g
Frequent Visitor

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 NumberStatusOpen DateClosed Date
1anom_completed6/19/2015 12:336/23/2015 9:21
2anom_completed6/19/2015 14:2510/7/2015 10:32
3anom_completed6/19/2015 14:3010/7/2015 10:32
4anom_completed6/19/2015 15:589/21/2016 9:50
5anom_completed6/19/2015 16:053/16/2016 14:01
6anom_completed6/20/2015 9:1312/3/2015 10:39
7anom_completed6/20/2015 9:4112/3/2017 10:41
8anom_completed6/22/2017 21:412/10/2018 10:04
9anom_completed6/23/2015 9:223/16/2016 14:01
10anom_completed4/10/2019 4:276/25/2019 12:02
11anom_completed4/10/2019 5:037/2/2019 4:46
12anom_completed4/10/2019 9:172/20/2020 14:24
13anom_completed4/10/2019 10:538/9/2019 14:08
1 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

21101201.jpg

 

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.

View solution in original post

10 REPLIES 10
v-jingzhang
Community Support
Community Support

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 - _closeCount

21101201.jpg

 

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.

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will 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. 

 

 

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors