Forum Discussion

k_rahul_g's avatar
k_rahul_g
Frequent Visitor
4 years ago
Solved

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 va...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
k_rahul_g
Frequent Visitor
4 years ago

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.