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

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

    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.