Count latest status per id per date in stacked bar chart

[sicaniaa90]

Dear fellow PBI enthusiasts!

As a beginner, I have reached the limit of my knowledge within PBI, and I really hope that some of you will help me out with this issue.

Goal

In a stacked bar chart I want to evaluate for every date what is the count of the latest status of all active cases.

Illustration of end result:

Tables overview:

Case Table:

Date Table:

Relationships:

My 'Date Table' and 'Case Table' have a one-to-many relationsship. I reckon that I will have to iterate over each date in the date table and execute a calculation.

Progress so far

I have managed to make a measure that evaluates for every row in 'Case Table' if the date of the status-change is the latest

The code:

Last_date =

VAR MaxDate =

    CALCULATE(

        MAX('Case Table'[Date])

    )

VAR CALC = CALCULATE(

    MAX('Case Table'[Status]),

    FILTER(

        'Case Table',

        'Case Table'[Date] = MaxDate

    ),

    USERELATIONSHIP('Case Table'[Date],'Date Table'[Date])

)

RETURN

IF(CALC <> "Done", CALC)

When I use this measure in a table in combination with a date-slicer im a able to retrieve the correct list of the latest status of an active case, and at the same time exclude cases that has the status "Done".

I would highly appreciate any help that can bring me closer to a solution.

Download PBIX-file:

In case you want to download the file I am working in.

https://drive.google.com/file/d/17-0TKpOI52mzexXoGkYoy_TFydZQ_bh2/view?usp=sharing

Best regards

Simon

[sicaniaa90]

Dear @amitchandak 

Thank you for pointing me in the right direction!

I seems that your code could be tweaked to solve the problem. However, I would need to make a calculated column in my 'Date Table' that executes [Sum Last Qty] for every date and only sum on the rows in the 'Case Table' that are below or equal to the current date selected:

Last Qty = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[ID] = max(Data1[ID]) ),Data1[Date])
return 
CALCULATE(sum(Data1[qty]), filter( (Data1), Data1[ID] = max(Data1[ID])  && Data1[Date] =_max))

Sum Last Qty = sumx(VALUES(Data1[ID]) , [Last Qty])

Do you have any ideas as to how i could do that? 

Best regards

Simon

[sicaniaa90]

Dear @Ashish_Mathur  

Thank you for your reply!

If this is the input 'Case Table':

ID	Status	Date
ID2	Created	02/01/2022
ID1	Created	02/01/2022
ID1	Wait	07/01/2022
ID3	Created	07/01/2022
ID2	In Progress	10/01/2022
ID1	In Progress	13/01/2022
ID3	Done	14/01/2022
ID1	Done	20/01/2022

The following 'Date Table' would solve my problem:

Date		Status (Created)	Status (Wait)	Status (In Progress)
01/01/2022		0	0	0
02/01/2022		2	0	0
03/01/2022		2	0	0
04/01/2022		2	0	0
05/01/2022		2	0	0
06/01/2022		2	0	0
07/01/2022		2	1	0
08/01/2022		2	1	0
09/01/2022		2	1	0
10/01/2022		1	1	1
Etc.

In this table, it should evaluate for every date, what is the latest date of each ID in the 'Case Table', that is below or equal to the selected date in the 'Date Table'.

In each column, the calculation should count only the ID's with this specific status. The visual in the end should look like this:

@Ashish_Mathur : Do you have an idea how to accomplish this?

Best regards

Simon

[inspire123]

Hi Simon, I'm stuck on exactly the same problem. Could you please paste the solution here? (the data file is no longer available....) Much much appreicated!!!!!!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[inspire123]

Hi, Could you please the formula you used to get this end result? THank you so much!!

[Ashish_Mathur]

Hi,

I do not have the file.

[inspire123]

This was the original file from the first original post: https://drive.google.com/file/d/17-0TKpOI52mzexXoGkYoy_TFydZQ_bh2/view?usp=sharing

Would you be so nice to replicate the solution again? I am so stuck.....

Thank you in advance.

[Ashish_Mathur]

Without referring to the question asked previoussly, please explain your question and show the exact result that you are expecting.

[voodoo23]

Hello, can you share this file again? I have same problem i now try to find answers. 

I need count status on current date (choosen) in pivot table (power bi).

I will be grateful.  

[Ashish_Mathur]

Hi,

I do not have the file.  Please share some data, explain the question and show the expected result.

[sicaniaa90]

Thank you so much Ashish, your solution worked wonders for me!

Best

Simon

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

In a simple Table, please show the expected result.

[amitchandak]

@sicaniaa90 , check if approach in these blog can help

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...