Forum Discussion

Allisond's avatar
Allisond
Icon for Advocate II rankAdvocate II
6 years ago

Census by admit/discharge By Date Range

I am new to PowerBI and struggling summarizing a measure I have created to calculate census days.
My goal is to sum census by day and also be able to calculate census days by date ranges.
 
My calculation is only allowing me to calculate census by day.   When I use ranges I am not getting the sum, I seem to be getting the amount of unique episodes.  The start and end date are on the same row.
 
Census =
CALCULATE([TotalEpisodes],
FILTER(VALUES(Census[StartDate]),Census[StartDate] <= MAX(Dates[DayDate])),
FILTER(VALUES(Census[BillingEndDay]),Census[BillingEndDay]>= MIN(Dates[DayDate])))
 
Unfortunately, when I try to date range this measure I am getting totals of the unique episodes rather that day by day summarization.
 
Example:
Census Count 1/1/20 = 720
Census Count 1/1/20 = 772
If totals are used in a Matrix I am getting = 801. 
 
Any help would be appreciated in creating something that would actually sum census days.  Thank you!
 
 
 
Need Help

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Do you have a relationship to your Dates table?  If so, you'll need an ALL() or REMOVEFILTERS() to remove the filter coming from that (added below).  Also, I think you could simplify your DAX as follows:

     

    Census =
    VAR __mindate =
    MIN ( Dates[DayDate] )
    VAR __maxdate =
    MAX ( Dates[DayDate] )
    RETURN
    CALCULATE (
    [TotalEpisodes],
    ALL ( Census ),
    Census[StartDate] <= __maxdate,
    Census[BillingEndDay] >= __mindate
    )
     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Allisond's avatar
      Allisond
      Icon for Advocate II rankAdvocate II
      6 years ago

      I need to be able to filter by location once I get the total days by date range.  Is there anyway not to include "ALL" and maintain filterability?  Sorry so many questions. 

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        Yes.  Not seeing your model, I suggested ALL(Census), but you can be more targeted on which filters you remove.  For example, you could replace ALL(Census) with either ALL(Date) or ALL(Census[StartDate], Census[EndDate).  I can't see original column names in reply window, so not sure if those are right, but you get the idea.  These would keep in place the filters on the other columns.  You could also try ALLSELECTED(Census) to keep filters coming from outside the visual.

         

        Regards,

        Pat

         

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Allisond ,

     

    You may change your formula like DAX below.

     

     

    Census =
VAR _date =
    SELECTEDVALUE ( Dates[DayDate] )
RETURN
    CALCULATE (
        [TotalEpisodes],
        FILTER (
            ALLSELECTED ( Census ),
            Census[StartDate] <= _date
                && Census[BillingEndDay] >= _date
        )
    )

     

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.