cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Power BI Monthly Update - February 2024

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

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors