Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dchoward
Regular Visitor

DAX Measure to Calculate row count based on a set of conditions and relative to a date

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:

 

Data.PNG

 

 

 Like the results to look something like this in a table, but can be sliced and diced with other data or other date intervals:

 

Result Example.PNG

 

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

3 REPLIES 3
Eric_Zhang
Employee
Employee

@dchoward

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.

 

PowerBI Results v1.PNG

However, I'm looking for something more like this:

 

 

I posted to quick, here are the expected results:

 

Expected Results.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.