DAX help

[kormosb]

Hi,

I would like to ask for help with the following:

I'd like to calculate cumulative result with TOTALYTD. The topic is testing.

I have the following table:

id	test execution date	status
1	2020.01.01	failed
1	2020.01.02	success
2	2020.01.01	success

I have this calculation:

CALCULATE(TOTALYTD(DISTINCTCOUNT('table'[id]),dim_date[Date]))

The result I get is for 2020.01.01 is 1 failed and 1 success (sum 2), which is fine. 

The problem is for 2020.01.02, where my cumulative result is 1 failed and 2 success (sum 3). But I would like to get 2 successes, as the 'id-1' was tested again and turned out to be successful) and id-2 as successful.

So basically if an [id] was tested again I don't want to add it to a cumulative results, just the last tested one.

Thanks for your help in advance.

Kind regards,

Benjamin

[v-yuaj-msft · Accepted solution]

Hi @kormosb ，

Based on your description, you can create some measures as follows.

1. Create measures.

Latest Execution Status =

var x1=MAXX(FILTER(ALL(table4),[id]=SELECTEDVALUE(table4[id])),[test execution date])

return

MAXX(FILTER(ALL(table4),[id]=SELECTEDVALUE(table4[id])&&[test execution date]=x1),[status])

Latest execution date = MAXX(FILTER(ALL(table4),[status]=[Latest Execution Status]&&[id]=SELECTEDVALUE(table4[id])),[test execution date])

1. Create a table visual.

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vivran22]

Hey @kormosb ,

You may try:

Count = COUNTAX(VALUES('Table'[id]),1)

Result:

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[kormosb]

Hey,

The table was a short example, I am not trying to calculate the cumulative result of the id-1, I would like a general sulition. I definitely need a cumulative DAX formula, something like totalytd.

Kind regards,

Benjamin