Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Allisond
Advocate II
Advocate II

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
Allisond
Advocate II
Advocate II

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

View solution in original post

3 REPLIES 3
Allisond
Advocate II
Advocate II

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

AlB
Super User
Super User

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 

SU18_powerbi_badge

 

amitchandak
Super User
Super User

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

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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