Forum Discussion

Honne2021's avatar
Honne2021
Helper II
4 years ago
Solved

PowerBi or Power Query Formula for dates before previous date

The Effective End Date is being done manually which is grueling cause there are so many dates in our file now. Is there a dax formula or m code that I can use to get the Effective End Date? The effec...
General Comment
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Honne2021 ,

     

    Got it. In this case for quick implementation, I'd suggest you use DAX:

    Effective End Date(DAX) = 
var _next= CALCULATE(MAX('Table'[Effective Date]),FILTER('Table',[Store]=EARLIER('Table'[Store])&& [Index]=EARLIER('Table'[Index])+1))
return IF(_next=BLANK(),BLANK(), _next-1)

    Best Regards,
    Eyelyn Qin

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Honne2021 ,

 

Got it. In this case for quick implementation, I'd suggest you use DAX:

Effective End Date(DAX) = 
var _next= CALCULATE(MAX('Table'[Effective Date]),FILTER('Table',[Store]=EARLIER('Table'[Store])&& [Index]=EARLIER('Table'[Index])+1))
return IF(_next=BLANK(),BLANK(), _next-1)

Best Regards,
Eyelyn Qin

Honne2021's avatar
Honne2021
Helper II
4 years ago

Thank you very much!