Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Dax Measurement Assistance

I am in need of assistance. What is the proper dax measurement to obtain the total daily inmate count (number of inmates currently incarcerated) from current date and 5yrs. back, per day. For example:

3-1-2024 - 340

3-2-2024 - 354

3-3-2025 - 342

The dataset includes, "Booking date", "Release Date", "Booking Number", and a Date Table.

 

Any help would be greatly appreciated.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    RossEdwards,

    I ended up creating the following measurement on my data table:

    ActiveInmatesCount =
    VAR CurrentDate = MAX('DimDate'[Date])
    RETURN
        CALCULATE(
           DISTINCTCOUNT('PrevFiveYrsJailing'[Booking#]),
            FILTER(
                'PrevFiveYrsJailing',
                'PrevFiveYrsJailing'[BookingDate] <= CurrentDate &&
                (
                    ISBLANK('PrevFiveYrsJailing'[ReleaseDate]) ||
                    'PrevFiveYrsJailing'[ReleaseDate] > CurrentDate
                )
            )
        )
     
    I was getting the same result where the amounts were not as close to the actual numbers I have.  I ended up linking this data table to a date table and making that relationship inactive.  Once I made the relationship inactive between the two tables, the data appears to more accurate.  I'm not sure why the inactive relationship worked, but it did.  I wanted to thank you for your time and assistance, much appreciated!   

     Alvina

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Making some guesses about your dataset but this is what came to mind for me.  Use this measure on a visual and it will take the last date in the context.  If its a monthly table, it will be the date at the end of the month. If its a daily table, it will be daily.

    Daily Count = var contextDate = MAX('DateTable'[Date])
var output = CALCULATE(
	COUNTROWS('Inmates'),
	ALL('Inmates'),
	FILTER(
		'Inmates',
		'Inmates'[Booking Date] <= contextDate &&
		'Inmates'[Release Date] >= contextDate
	)
)
RETURN
output

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you for the prompt reply. This is the output.  I was hoping to get a TOTAL count of currently incarcerated inmates, per day.  For example on 3/23 there would have been approximately 350 inmates in jail, that have been been booked in within my time range and have not been released (time range is 5yrs back from current date).

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        I suspect your issue is that you have used "Booking Date" in your visual.  Replace that with the "Date" column from your Calendar table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    RossEdwards,

    I ended up creating the following measurement on my data table:

    ActiveInmatesCount =
    VAR CurrentDate = MAX('DimDate'[Date])
    RETURN
        CALCULATE(
           DISTINCTCOUNT('PrevFiveYrsJailing'[Booking#]),
            FILTER(
                'PrevFiveYrsJailing',
                'PrevFiveYrsJailing'[BookingDate] <= CurrentDate &&
                (
                    ISBLANK('PrevFiveYrsJailing'[ReleaseDate]) ||
                    'PrevFiveYrsJailing'[ReleaseDate] > CurrentDate
                )
            )
        )
     
    I was getting the same result where the amounts were not as close to the actual numbers I have.  I ended up linking this data table to a date table and making that relationship inactive.  Once I made the relationship inactive between the two tables, the data appears to more accurate.  I'm not sure why the inactive relationship worked, but it did.  I wanted to thank you for your time and assistance, much appreciated!   

     Alvina