Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Trying to create a measure that will count the number of row where the record is open/active, (open date is in the past, the close date is in the future or is null) and to bucket the count into aged buckets like 0-29, 30-59 etc. that is relative to a fixed date in the report/table.
Here is a sample of the data set:
Like the results to look something like this in a table, but can be sliced and diced with other data or other date intervals:
I do have a separate date table with all the days of the year.
I've tried all sorts of Calculate DAX formulas with filters, but just not getting the right results.
Any help or suggestions would be very appreciated.
Thanks
Try to create a calculated column in the transaction table. You can replace the TODAY() with the fixed related date.
Column = VAR days = INT ( IF ( ISBLANK ( Table1[ClosedDate] ), TODAY () - Table1[Open Date], Table1[ClosedDate] - Table1[Open Date] ) ) RETURN SWITCH ( TRUE (), days < 30, "0-29 days", days < 60, "30-59 days", days < 90, "60-89 days", "90+ days" )
and another column in your date table.
The last day of month = ENDOFMONTH('calendar'[Date])
Map those two tables in a one-to-many relationship and put the first calculated column as rows and the second as column in a Matrix visual.
I truely appreciate offering a solution. I upsated my sample data slightly and tried your solution, and below are my results.
However, I'm looking for something more like this:
I posted to quick, here are the expected results:
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |