The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
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:
Case Table:
Date Table:
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.
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.
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
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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
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
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!!!!!!
Hi,
You may download my PBI file from here.
Hope this helps.
Hi, Could you please the formula you used to get this end result? THank you so much!!
Hi,
I do not have the file.
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.
Without referring to the question asked previoussly, please explain your question and show the exact result that you are expecting.
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.
Hi,
I do not have the file. Please share some data, explain the question and show the expected result.
Thank you so much Ashish, your solution worked wonders for me!
Best
Simon
You are welcome.
Hi,
In a simple Table, please show the expected result.
@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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |