November 8, 2018 at 7:50 pm #11140
- Started: 1
- Replies: 0
- Total: 1
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.
1/20/2016 COLON SSI
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!!
You must be logged in to reply to this topic.