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
jbetkowska
Frequent Visitor

Count opened cases by day with "for" loop

Hello,

 

I have following issue and I don't know how to start writting Dax measure (if it is possible in DAX).

Lets say we have following data table:

IDopened_atclosed_at
1opened_dateclosed_date
2opened_dateopened_date

 

I would like to achieve a chart with dates on X axis - let's say last 30 days that will show count of ID's  that opened_date <= day(i) and closed date > day(i) - meaning I would like to have a chart that shows number of events that were not closed on specific day. I cannot create aggregates in DataBase because it is not mine and I have only RO access. 

Any ideas how to start?

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Try

Open cases =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        REMOVEFILTERS ( 'Date' ),
        'Table'[Start date] <= MaxDate
            && (
                ISBLANK ( 'Table'[End Date] )
                    || 'Table'[End date] > MaxDate
            )
    )
RETURN
    Result

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

Try

Open cases =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        REMOVEFILTERS ( 'Date' ),
        'Table'[Start date] <= MaxDate
            && (
                ISBLANK ( 'Table'[End Date] )
                    || 'Table'[End date] > MaxDate
            )
    )
RETURN
    Result

Thank you for the hint. Seems like I have solutions thanks to you!

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.

Top Solution Authors