Reply
Allisond
Advocate II
Advocate II
Partially syndicated - Outbound

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

Syndicated - Outbound

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

View solution in original post

3 REPLIES 3
Allisond
Advocate II
Advocate II

Syndicated - Outbound

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

AlB
Community Champion
Community Champion

Syndicated - Outbound

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

Syndicated - Outbound

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)