How to avoid historical data in table view (Should see the record based on latest date).

[Anonymous]

Hi All,

Hope you are doing well, I have a requirement (I have tried in many  ways but nothing worked). Please help with this requirement.

Req 1:

• Show the records only of the latest date based of the Id or Id and Status. The view should be as shown below.
  

Req 2:

• In KPI "Sum of QTY" must to be 5 instead of 15 (the Qty is duplicated based on the Id).

I apperciate you for your time in helping me.

Thank you,

Regards
Vivek B

[amitchandak · Accepted solution]

@Anonymous ,Try measures like, add to visual with all other field as not summarized

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])

Follow the approach in my blog  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-f1c839ee884e

[Anonymous]

Hi Amit,

I have one doubt. from the below table I need to get Total Qty=55 instead of 152. Can please help me here. Based on distinct Project ID I need the total Qty. 

Thank you for your time.

Regards 

Vivek B

[Anonymous]

Hi Amit,

Thanks for quick response, and the DAX you provided is also working fine.

Regards

Vivek