Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My data is in the following format
ReportDate | UpdateDate | Column A | Column B |
31st December 2020 | 20/12/2020 8:00:00 | 10 | 80 |
31st December 2020 | 15/12/2020 10:30:00 | 88 | 00 |
31st December 2020 | 01/12/2020 8:00:00 | 44 | 32 |
30th November 2020 | 29/11/2020 10:30:00 | 90 | 99 |
30th November 2020 | 26/11/2020 14:00:33 | 44 | 67 |
30th November 2020 | 15/11/2020 15:00:44 | 50 | 20 |
EDIT :::
Also I have a DateDimension in my model. I donot want to sum the data , but select only the data which is for the latest update date.
How can I do it?
@parry2k The solution seems to be fine but problem is that donot want to perform any aggregations on the QTY(Column A and Column B)
So in the report, I would like to have only the updated data for each reporting month like below
ReportDate | UpdateDate | Column A | Column B |
31st December 2020 | 20/12/2020 8:00:00 | 10 | 80 |
30th November 2020 | 29/11/2020 10:30:00 | 90 | 99 |
Solved! Go to Solution.
Hi @Sachy123 ,
According to your requirements, I did the following test and did not aggregate column A and column B. I created Filter_column measure filters out the max date under the group. Then, the customer filter is used to filter out “is not blank” rows.
Here is the sample .pbix file.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Sachy123 ,
According to your requirements, I did the following test and did not aggregate column A and column B. I created Filter_column measure filters out the max date under the group. Then, the customer filter is used to filter out “is not blank” rows.
Here is the sample .pbix file.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks but I am using a Date Dimension which is linked to Report date . but the problem is that it gives me multiple instances of Update Date.. 😞
And my DateDimension is without time, so it cannot actually find records if I ocnnect the datekey to the UpdateDate Column..
E.g. 20/12/2020 8:00:00 is not equal to 20/12/2020 0:00:00
@Sachy123 you can add following measure for both the columns
Update Date Max =
CALCULATE ( MAX ( Table[UpdateDate] ), ALLEXCEPT ( Table, Table[ReportDate] ) )
and use above measure to get the quantity
Max Qty Update Date =
VAR __maxUpdateDate = [Update Date Max]
RETURN
CALCULATE ( SUM ( Table[Qty] ), ALLEXEPT ( Table, Table[ReportDate] ), Table[UpdateDate] = __maxUpdateDate
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |