Forum Discussion

isidnis's avatar
isidnis
Regular Visitor
7 years ago

START OF MONTH different per category

Hello, I have this sample data: where start of month has been calculated as: startofmonth = CALCULATE(STARTOFMONTH(DataABS[Fecha]),ALLNOBLANKROW(DataABS[max],DataABS[Fecha])) My problem is th...
MatejZukovic's avatar
MatejZukovic
Icon for Resolver I rankResolver I
7 years ago

Hi!

 

you may consider using EARLIER if you want to calculate the earliest date per device ID:

 

startofmonth = CALCULATE(MIN(Table[Table.Fecha].[Date]), FILTER(Table, Table[deviceid]=EARLIER(Table[deviceid])))

Hope it helps!

Matej

 

 

isidnis's avatar
isidnis
Regular Visitor
7 years ago

Thanks a lot for your reply.

 

 

It doesn´t work, it says:

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

and I don´t think it would work in subsequent months.

 

  • MatejZukovic's avatar
    MatejZukovic
    Icon for Resolver I rankResolver I
    7 years ago

    Hi Isidnis,

     

    It would be good if you share some sample data. 

     

    Another option would be creating 2 columns : 

     

    1. Concatenate deviceid & month-year of the date

    Month_DeviceID = FORMAT(Table[Fecha].[Date],"YYYY-MM") & ":" & Table[Table.deviceid]

    2. Calculate minimum date value per newly created Month_DeviceID:

     

     

    mindate = CALCULATE(MIN(Table[Table.Fecha]),ALLEXCEPT(Table,Table[Month_DeviceID]))

     

    Let us know if it works!