Home Forums Power Pivot Calculating difference in dates located within a single column

This topic contains 0 replies, has 1 voice, and was last updated by  jgolob 1 week ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #11140

    jgolob
    Participant
    • Started: 1
    • Replies: 0
    • Total: 1

    Hello everyone,

    I’m on a mission to show the power of PowerPivot within my hospital, but I am relatively new to DAX.  I have a table of preventable harms that are monitored by CMS.  There are a total of 18 different harms with each harm being associated with the date of occurrence.  I am trying to dynamically calculate the days between harms filtered by the harm.  The table looks like this.
    Date Harms
    1/8/2016 CAUTI
    1/8/2016 PVAP
    1/8/2016 C.Diff
    1/9/2016 CAUTI
    1/12/2016 PSI13
    1/20/2016 COLON SSI
    1/30/2016 CAUTI
    I have attempted to create a rank calculated column based on harm and date of event.  Then create another calculated column using the EARLIER function to get the date of the previous harm.  Finally, create a measure to calculate the difference between dates and then average of this difference.  I am really struggling!  This is an example of what I am looking for.  If the user selects CAUTI from a slicer and January 2016 from a time line, the following would be created behind the scenes:
    Date Harm Rank PrevDate Days Between Harm
    1/8/2016 CAUTI 1
    1/9/2016 CAUTI 2 1/6/2016 1
    1/30/2016 CAUTI 3 1/9/2016 21

    I would then create my measures with the Days between harms column. The problem becomes when I try to use the slicer/filter to get the harm.  Everything seems to break.  Any suggestions would be greatly appreciated.  Thanks!!

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.