Forum Discussion

Nicram's avatar
Nicram
Regular Visitor
4 years ago

Aggregate Values for corresponding MAX date.

Hello Community!

I've been struggling for a few days with a running total measure.

So I have a dataset of issue ID, date, and status. It's an activity registry.
I'm trying to create a visual with running totals that counts a number of issues with per status. It should look like the graph below.

 

Running totals are simple, the problem is to do pre-filtering on the last status on a given date.

I managed to get the status of the latest TimeStamp by MAXX and then a value corresponding to that time by:

 

 

M_LastMainStatus_SELECTEDVALUE =
VAR _maxTimeStamp =
    MAXX ( 'Table1', 'Table1'[TimeStamp] )
VAR _LastStatus =
    CALCULATE (
        SELECTEDVALUE ( 'Table1'[Status] ),
        'Table1'[TimeStamp] = _maxTimeStamp
    )
RETURN
    _LastMainStatus

 

 

This measure gives nice result in a table/matrix. However, I have a hard time incorporating it into a running total that 'says': on day one we had 5 active and 2 closed issues, on day two we had 4 active and 5 closed issues and so on.

 

Sample dataset below. An issue can be closed and active multiple times per day. It's a simplified version of my original table with more statuses (blanks included).

IDTimeStampStatus

1

01/01/2020 7:00Active
101/01/2020 8:00Closed
102/01/2020 8:00Active
201/01/2020 10:00Active
201/01/2020 11:00Closed
202/01/2020 10:00Active
202/01/2020 11:00Closed
301/01/2020 7:00Active
301/01/2020 8:00Active
302/01/2020 10:00Closed
302/01/2020 11:00Closed


I've tried DAX to combine CALCULATE, FILTER, COUNTROWS, SELECEDVALUE, ALL but I did not come close desired result. Existing topics on the forum didn't help either.

Do you have any suggestions? Have you ever faced a similar task?

I'd appreciate any kind of help!
Cheers,
Nicram

Need Help
Show and Tell

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Nicram , Create a new date table and join it with date part of time stamp

    new column

    Date = datevalue([TimeStamp])

     

    then try measures like

    open = calculate(countrows(Table), Filter(all('Date'),'Date'[Date] <= Max('Date'[Date] )), filter(Table, Table[Status] = "Active")) -
    calculate(countrows(Table), Filter(all('Date'),'Date'[Date] <= Max('Date'[Date] )), filter(Table, Table[Status] = "Closed"))

    Active = calculate(countrows(Table),filter(Table, Table[Status] = "Active"))
    closed = calculate(countrows(Table), filter(Table, Table[Status] = "Closed"))