Having trouble aggregating statuses over time with a status log data

[Allan67823]

Hi All!

I have a dataset very similar to the image below, where I have a rolling log of items that progresses through different statuses or stages over time. For each time the item status changes, it is logged into the database. each log having:

• The datetime of the status change of the item (datetime in the img)

• unique identifier for the item (item_ID)

• Status they changed to.

I am still quite a beginner with DAX and would like your help in regards to the DAX formula.

What I want to do is to aggregate how many items are in each status over time (per day/month/year). This way I will be able to see the number of items in each status historically.

The actual dataset that I'm working with is a lot bigger and I hope I'm getting my point across using the sample image. Below is an example of the end goal.

Example of the end goal: (note that numbers don't match the sample image)

I have 100 (may increase in the future) items and each item will progress through stages over time.

	Stage A	Stage B	Stage C
March 2021	91	6	3
Apr 2021	60	30	10
May 2021	25	50	25
June 2021	5	15	80

A measure that will let me aggregate and give these counts is what I'm after.

For each time period, it gives me the count of items in each stage, only taking into account their latest stage up to that time period (March 2021 will look at every row from the start of time up to March 2021 and count only the latest status). Looking at the table, in May 2021, there are only 25 items left that have 'Stage A' as its latest Stage value then 50 for stage B and so on..

I presume the solution would be the exact same for other datasets such as users + their statuses for when a company would like to know how effective their customer conversions would be. I also need to do this for my next Power BI report so this will help me a lot!

Thanks in advance!

[v-jingzhang]

Hi @Allan67823 

Struggled hours but still didn't find a solution unfortunately...

I use below codes to create a calculated table. When given a fixed date, it seems get the correct latest status for each item ID by that time. But when I use it in a measure as a variable table, change the _maxDate variable to get the max date in its current context and count the numbers of each status from the variable table, it always fails to get the correct counts. Not sure why and how to deal with it...

Tablett = 
VAR _maxDate = DATE(2021,5,28) // This date is not fixed when in a measure
VAR _table1 = FILTER(ALL('Table'),'Table'[datetime]<=_maxDate)
VAR _table2 = ADDCOLUMNS(SUMMARIZE(_table1,'Table'[item_ID],"max_date",MAX('Table'[datetime])),"last_status",CALCULATE(SELECTEDVALUE('Table'[Status]),'Table'[item_ID]=EARLIER('Table'[item_ID]),'Table'[datetime]=EARLIER([max_date])))
RETURN
_table2

Hope someone could give more ideas and help find the solution! 

Regards,
Community Support Team _ Jing

[Allan67823]

Thank you so much for your effort! I've tried different Dax formulas to solve this problem and I am still unsuccessful in getting the correct formula and values...

It seems that not many people have this problem on forums online, I wonder how other organisations are aggregating user statuses over time if at all. I'm starting to think that aggregations from the database (source) via SQL script would be the way to go. If it is this hard in DAX, there's gotta be a better way to solve this problem right? Only downside of the SQL aggregation would be that we will be unable to drill down on who was X status in Y timeframe.