Power BI Previous Row based on criteria (condtion)

[Janaya]

Dear Power BI community:

I would really appreciate if you could help me out with the following issue.

I need to know how long it takes the student to complete a level

Values ​​in green are expected

Thanks in advance!

[TomMartens · Accepted solution]

Hey @Janaya ,

I'm sure you can adapt this article The previous value - Mincing Data - Gain Insight from Data (minceddata.info) to your needs.

I'm wondering why the previous log of the 31st of January 2021, is 23rd of November and not the 24th of December 2021. it seems there is a rule that can be applied as a similar pattern appears on 31st of May 2021.

Regards,

Tom

[v-angzheng-msft · Accepted solution]

Hi, @Janaya 

Create 4 measures like below to do this:

1. Levels Completed column work or not

_isTrue = 
var _1=IF(MAX('Table'[Levels Completed])>0,1,BLANK())
var _r=IF(HASONEVALUE('Table'[Levels Completed]),_1,BLANK())
return _r

2.Previous login date

_Previous login = 
var _table=FILTER(ALLEXCEPT('Table','Table'[Student]),'Table'[Last Login]<MAX('Table'[Last Login]))
var _last=CALCULATE(LASTNONBLANK('Table'[Last Login],[_isTrue]),_table)
var _first=CALCULATE(FIRSTNONBLANK('Table'[Last Login],MIN('Table'[Levels Completed])),_table)
var _Pre=IF(_last=BLANK(),_first,_last)
var _ifTrue=IF([_isTrue]=1,_Pre)
return _ifTrue

3. Datediff

_Diff = 
var _diff=DATEDIFF([_Previous login],MAX('Table'[Last Login]),DAY)
var _if=
    IF(
    HASONEVALUE('Table'[Last Login]),
    _diff,
    AVERAGEX(ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),'Table'[Last Login],"pre",[_Previous login]),"diff",DATEDIFF([pre],[Last Login],DAY)),[diff])
    )
return _if

4. average of datediff

_average = 
var _t=SUMMARIZE('Table','Table'[Student],"diff",[_Diff])
var _result=AVERAGEX(_t,[diff])
return _result

Then to create a card visual to show the average of student's datadiff.

Result:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens · Accepted solution]

Hey @Janaya ,

I'm sure you can adapt this article The previous value - Mincing Data - Gain Insight from Data (minceddata.info) to your needs.

I'm wondering why the previous log of the 31st of January 2021, is 23rd of November and not the 24th of December 2021. it seems there is a rule that can be applied as a similar pattern appears on 31st of May 2021.

Regards,

Tom