RemoveFilter Function Not Working Within Calculate when Last.Date is used as a Filter

[Anonymous]

Hi All, 

I've simplified my Data/Measure and attached a screenshot.

The Formula within my Measure is as follows:

CountRows for LastDate = CALCULATE(COUNTROWS(Sheet1),LASTDATE(Sheet1[Date]),REMOVEFILTERS(Sheet1))

I want to count rows only for the last date within the date column. Additionally, Ignore any Filters that are applied within the visuals. 

The desired value here should be 4. 

This works for all scenarios in the screenshots attached. 

Except when "Pre 10" or "01 January 2023" is Selected within the slicers. 

The Measure should Ignore All filters within the table. 

I think this is due to the fact that neither "2 Jan 2023" nor "Pre 10" have any values for the last date in the data.

Which is wrong, either there's an error within PowerBI or  I'm not understanding how these functions worked, (Most likely the latter). 

Would someone be able to suggest an alternative to achieve this?

 

  

 

 

  

[Anonymous]

Hi Amitchandak, do you know why my formula doesn't work, is this because of a PowerBI error? 

[amitchandak]

@Anonymous , Try like

CountRows for LastDate =
Var _max = maxx(allselected(sheet1), Sheet1[Date])
return
CALCULATE(COUNTROWS(Sheet1),filter(Sheet1, Sheet1[Date]= _max))

OR

CountRows for LastDate =
Var _max = maxx(all(sheet1), Sheet1[Date])
return
CALCULATE(COUNTROWS(Sheet1),Sheet1[Date]= _max)

refer if needed

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-f1c839ee884e