cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

## 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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors