Difference between several dates in the same column

Question

Difference between several dates in the same column

Navigation

#1 by (0 votes)

0

I have the following problem ..

I have the following table:

Iwanttocreateacalculatedcolumnthattellsmethedifferenceofdaysbetweentwodatesofthesamecode(COD),thedifferenceshouldbecalculatedbasedonthedatebeforetheline.Forexample:

UsingCODB

COD|DATE|DaysofDifference

B|05/01/2018|

B|09/01/2018|4

B|12/01/2018|3

Intheexampleimagethecodes/datesaresortedinsequence,butinrealitytheyareoutoforder.

ItriedtousethefollowingsentenceinDAX:

DATEDIFF(Testing[DATE];FIRSTDATE(FILTER(ALL(Testing[DATE]);Testing[DATE]>EARLIER(Testing[DATE])));DAY)

ExplainingwhatI'vetried: MakethedifferencebetweenthedateonthelineandusingtheEARLIERfunctiontogetthemostrecentdateoutofthecurrentone.

However,Igotthefollowingresult:

I'm not able to filter COD , so that the analysis of 'EARLIER' is performed only in the same 'group', so I understand PowerBI is considering all dates. >

Any ideas?

powerbi dax

asked by anonymous 04.01.2019 / 19:40

1 answer

Query Sql to provision bank balances ValueError: Can not assign "'1'": "User.campus" must be a "Campus" instance. Error in Super User registration in Django

score 0 · Accepted Answer

After a few tries, I also asked in the OS. And I got the following solution that worked very well, and can be found completely here:

Difference between multiple dates in the same column based on category

Here's the solution:

Days_diff =
VAR StartDate =
    CALCULATE (
        LASTDATE ( Testing[DATE] ),
        FILTER (
            ALLEXCEPT ( Testing, Testing[COD] ),
            Testing[DATE] < EARLIER ( Testing[DATE] )
        )
    )
RETURN
    DATEDIFF ( StartDate, Testing[DATE], DAY )

And the explanation provided by the user Alexis ..

CALCULATE to remove the entire context of the line except COD / strong>, since that's what we're putting together.

Note: The EARLIER function is not a date / time function, but a reference to an earlier line context (before entering the FILTER function). This allows us to return a level when we are nesting functions.

Then you just get the DATEDIFF .