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.

v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

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

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.