Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Value from previous row grouping within groups

Dear Community, pelease help

 

New but eager to learn Power BI:-)  

I am strugling with finding a solution that gives me previous Date/row value and also grouping it by the key/items

This it to have a history that shows start and end dates.

 

This is how it would look like:

 

Before:

ItemCountryDate
1DK03/04/2020
2DK03/04/2020
4SE04/04/2020
2DE03/05/2020
3DK03/05/2020
3DK21/05/2020
1SE05/04/2020
1DE10/04/2020
2SE03/06/2020
4DE10/04/2020
2DK10/06/2020
3DK21/05/2020
3SE21/05/2020
1DE20/04/2020

 

Aften:

ItemCountryChangeDatePreviousDateWeekdays
1DK03/04/202003/04/20201
1SE05/04/202003/04/20201
1DE10/04/202005/04/20205
1DE20/04/202010/04/20207
2DK03/04/202003/04/20201
2DE03/05/202003/04/202021
2SE03/06/202003/05/202023
2DK10/06/202003/06/20206
3DK03/05/202003/05/20200
3DK21/05/202003/05/202014
3DK21/05/202021/05/20201
3SE21/05/202021/05/20201
4SE04/04/202004/04/20200
4DE10/04/202004/04/20205

 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous ,

    You can get

    previous Date = coalesce(maxx(filter(Table,[Item] =earlier([Item]) && [Country] =earlier([Country]) && [ChangeDate] <earlier([ChangeDate]) ),[ChangeDate]),[ChangeDate])

     

    Date diff = ( [ChangeDate] ,[previous Date],Day)

     

    For the business days you need to use calendar . refer

    https://www.sqlbi.com/articles/counting-working-days-in-dax/