Count of column based on date

[Begbie]

Hello everyone,

I require some help or someone to point me in the right direction please.

I have a table which has an output of below:

What I would like to achieve is a count of the status column (it is either Ok or Nok), but based on the latest point in the day. As some entries might be Ok earlier in the day, but then to Nok later or vice versa.

My current implementing I have is:

CALCULATE(COUNTROWS(History), History[Status] = "Ok")

This will give me a count of 9 rows, but should be 3 as their is only 3 productcode entries.

I'm sure I need to use something like MAX(History[EvaluatedAt]) within the above expression, but can't seem to understand how to do this.

ETA: There is data on previous days and will be going forward, so am representing this in a line chart to some some historical data

Any help would be greatly appreciated 😊

Thank you

[Begbie · Accepted solution]

Just to close this one off. As I was pulling data from a SQL database, I managed to create a SQL query that changed the data as it came into PBI which presented the data on how I wanted it to be.

[NikhilChenna]

Hi @Begbie ,

Try the below dax- 

Create a new summarized table by going in to the modelling tab and by clicking the new table option, 

Summarized table = 
Summarize(

'history',

'history'[ProductCode],

"MAx Date",MAX('history'[EvaluatedOn])
)

After this create a caculated column of concat in both the tables with Productcode and evaluated date.

Then create a another caculated column in the summarized table using the lookupvalue function take the status from the previous table to the summarized table based on the concat column.

This will solve your issue.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Begbie]

Hi @NikhilChenna 

Thank you for your help. I've done some of what you have mentioned but this is only showing the most recent date in the summarised table.

I have edited my opening post to reflect that the dates will be in the past and also added going forward, so will need to get a count of Ok or Nok for each product for each day, as this will be represented in a line graph to show historical numbers.