Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to find the time between multiple dates (aka frequency)

I have a table with a column that records the date when service was done at a certain location. I would like to find the time between each date, not just two dates. Hoping to turn this into an "average time between service" card. Thanks!

Need Help

5 Replies

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

    Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Greg_Deckler 


      I want the column to show the # of days between the row above. For example, the last row is 40 days since the row above, etc. If this is not doable,  I would at least like to find the "Average Time Between Service" to show how often we have to spend (i.e. average interval of 25 days between service)

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

        Anonymous I'm thinking something like:

        Time Between Measure =
  VAR __CurrentDate = MAX('Table'[Date])
  VAR __PreviousDate = MAXX(FITLER(ALL('Table'),[Date]<__CurrentDate),[Date])
RETURN
  (__CurrentDate - __PreviousDate) * 1.
  • ziyabikram96's avatar
    ziyabikram96
    Icon for Helper V rankHelper V
    5 years ago
    Column =
VAR temp =
    TOPN (
        1,
        FILTER (
            Table1,
            Table1[cost] = EARLIER ( Table1[cost] )
                && Table1[Date] < EARLIER ( Table1[Date] )
        ),
        [Date], DESC
    )
RETURN
    DATEDIFF ( MINX ( temp, [Date] ), Table1[Date], DAY )

    Create calculated column and use this change it with respect your rquirements hope this will work