Aggregate Values for corresponding MAX date.

[Nicram]

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).

ID	TimeStamp	Status
1	01/01/2020 7:00	Active
1	01/01/2020 8:00	Closed
1	02/01/2020 8:00	Active
2	01/01/2020 10:00	Active
2	01/01/2020 11:00	Closed
2	02/01/2020 10:00	Active
2	02/01/2020 11:00	Closed
3	01/01/2020 7:00	Active
3	01/01/2020 8:00	Active
3	02/01/2020 10:00	Closed
3	02/01/2020 11:00	Closed

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

[amitchandak]

@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"))