cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

1 ACCEPTED SOLUTION

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

3 REPLIES 3

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

Super User

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors