Zeros in Matrix Rather Than 1 per Day (Census Values)

[Allisond]

Good Morning,

My table has columns including a start and end date column.   From that column I need to show the census per day in a date range. 

Census =

VAR StartDate = VALUE(SELECTEDVALUE('Patient Data'[Start Date]))

VAR EndDate = VALUE(SELECTEDVALUE('Patient Data'[End Date]))

VAR MinDate = VALUE(MIN(Dates[DayDate]))

VAR MaxDate = VALUE(MAX(Dates[DayDate]))

RETURN

IF(AND(StartDate <= MinDate, EndDate>= MinDate),

MIN(EndDate,MaxDate) - MinDate,

IF (AND(AND(StartDate >= MinDate, StartDate<= MaxDate), EndDate >= MinDate),

MIN(EndDate,MaxDate) - StartDate + 1, BLANK()))

Which is working great if I use a Matrix and just total for a date range.   As soon as I add the Date to the Columns I get "0" in each column which should count for 1 day but the totals are correct.  

Days of Mo                       1  2  3  4  5  6  7           Total

Patient1                               0  0  0  0  0            5

Patient2                            0  0  0                     3

My totals are also blank.  I have the Measure format set up as a whole number and the date field are dates.  

Can anyone help me with what I a doing wrong that the days are rendering with "0" rather than a "1"? 

Thank you

[Allisond · Accepted solution]

Found it.  Added +1 to the first if Return.   Thanks!

[AlB]

Hi @Allisond 

Try this measure that use the [Census] measure you already have

Measure =
SUMX ( DISTINCT ( Dates[Days of Month] ), [Census] )

 Dates[Days of Month] is the field you are adding to the visual that makes it not work

If the above doesn't work I'd need a more detailed explanation in what fields you are using in the visual and how

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@Allisond ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.