Forum Discussion

Mohan128256's avatar
Mohan128256
Icon for Helper IV rankHelper IV
2 years ago
Solved

Get Prev Dates from Dates Column

Hello All,

 

I have these below list of dates 

Snapshot Date
4-16-2024
4-11-2024
4-10-2024
4-8-2024
4-5-2024
4-4-2024
4-3-2024
3-27-2024
3-22-2024
3-21-2024
3-20-2024
3-19-2024
3-18-2024

 

And I would like to get the previous dates if they are continous only.

here from 03/18/2024 to 03/22/2024 the dates continous so to the previous dates would be as usual.

But from 03/22/2024, the next date we have is 03/27/2024 which is not continous.

So here the previous date should be blank.

And followed by 04/03/2024, we have 04/04/2024, 04/05/2024 which are continous, so it should give the previous dates as usual.

Like wise..I am expecting the output of previous dates as below screenshot.

 

 

 

Any help here please.

 

Thanks,

Mohan V.

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Mohan128256 OK, modified Cthulhu to work with your situation. It's a column and updated PBIX is attached:

    Chtulhu = 
    VAR __Date = [Snapshot Date]
    VAR __Table = FILTER( 'Table', [Snapshot Date] <= __Date )
    VAR __Table1 = ADDCOLUMNS( __Table, "__Diff", ( [Snapshot Date] - MAXX( FILTER( 'Table', [Snapshot Date] < EARLIER( [Snapshot Date] ) ), [Snapshot Date] ) ) * 1. )
    VAR __Max = MAXX( __Table1, [Snapshot Date] )
    VAR __MaxStart = MAXX( FILTER( __Table1, [__Diff] > 1 ), [Snapshot Date] )
    VAR __Table2 = FILTER( __Table1, [Snapshot Date] >= __MaxStart )
    VAR __Result = COUNTROWS( __Table2 ) - 1
RETURN
    __Result

7 Replies

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      2 years ago

      Mohan128256 Sure:

      Column = 
    VAR __Date = [Snapshot Date]
    VAR __PreDate = MAXX( FILTER( 'Table', [Snapshot Date] < __Date ), [Snapshot Date] )
    VAR __Result = IF( ( __Date - __PreDate ) * 1. = 1, __PreDate, BLANK() )
RETURN
    __Result

      It's a variation of See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
      The basic pattern is:
      Column = 
        VAR __Current = [Value]
        VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

        VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
      RETURN
       ( __Current - __Previous ) * 1.

       

      PBIX is attached below signature. And here is a measure version:

      Measure = 
    VAR __Date = MAX([Snapshot Date])
    VAR __PreDate = MAXX( FILTER( ALLSELECTED('Table'), [Snapshot Date] < __Date ), [Snapshot Date] )
    VAR __Result = IF( ( __Date - __PreDate ) * 1. = 1, __PreDate, BLANK() )
RETURN
    __Result
      • Mohan128256's avatar
        Mohan128256
        Icon for Helper IV rankHelper IV
        2 years ago

        Greg_Deckler  thats why you are my guardian angel 😊

         

        It worked like a charm. 

         

        Apologies here that i did not given the full details of what i am looking for here.

         

        Actually I am looking to calcaulate the datediff when it is continous dates only.

        So thought of getting the previous date when the dates are only continous dates would solve it.

         

        But it think i am wrong now..when i do the datediff with the original column to this field it jus gives as 1 always.

         

        the expected result which i am looking is actually as this.

         

         

         

        Apologies...but really need your help as i have been trying to get this done for a long time.