cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Date filter in a measure is not working!?

Hi All,

I'm having a real pain trying to count rows with some date filters. So I have a table [Table A] with a date field which is formatted to just date type in Query Editor.

I then have a Date table which has a Date field which is also formatted to just date type in Query editor and these two tables are joined by this field on a Table A * to 1 Date table.  The Date table also has another coloum which shows the start date of each financial period.

I have created a measure below which basically returns the start of the fin month based on what date is selected by the user:

Fin Date 01 = CALCULATE(MIN('Date'[Start of Financial Month]),FILTER('date','Date'[Date] = SELECTEDVALUE('Date'[Date])))

The next step is my measure to count rows:
Count = Calculate(countrows('Table A'),FILTER('Table A', [Date] = [Fin Date 01]))
and this does not seem to work.

However if I create another date measure like this...
Test_date = DATE ( YEAR ( TODAY () ), 5, 30 )
And swap that in then it works and I get a count for 30/05/22

Any clue on what I'm doing wrong??

Thanks

Ben
2 ACCEPTED SOLUTIONS
Community Champion

@Ben81 try this:

Fin Date 01 =
VAR _selected_date = SELECTEDVALUE('Date'[Date])
RETURN
CALCULATE(MIN('Date'[Start of Financial Month]), Date'[Date] = _selected_date)

Next step:
Count =
VAR _fin_date = [Fin Date 01]
RETURN
Calculate(countrows('Table A'), 'TableA'[Date] = __fin_date)

Helper I

Worked like a charm, thanks! 🙂

9 REPLIES 9
Community Champion

@Ben81 try this:

Fin Date 01 =
VAR _selected_date = SELECTEDVALUE('Date'[Date])
RETURN
CALCULATE(MIN('Date'[Start of Financial Month]), Date'[Date] = _selected_date)

Next step:
Count =
VAR _fin_date = [Fin Date 01]
RETURN
Calculate(countrows('Table A'), 'TableA'[Date] = __fin_date)

Helper I

Worked like a charm, thanks! 🙂

Community Champion

my pleasure :))
Listen, check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. Please give it a thumbs up over there if you liked it 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543

Helper I

Hi mate, sorry about this but I need a bit more help on the measure above! I realised once I got it working I acutally need to to find everything equal or greater than.

I tried just changing it to >= _fin_date) but because of the date slicer it will only show me results for the same date and not greater than 😞

Community Champion

@Ben81 not sure I understand.

Do you want to do a quick zoom call?

Helper I

Sorry mate I can't do a zoom call but I amended your code below. Essentially I want a count everything on 30/05/22 up to say today. I thought just changing the parametor to >= would do the trick but that doesn't seem to work.

Fin Date 01 =
VAR _selected_date = SELECTEDVALUE('Date'[Date])
RETURN
CALCULATE(MIN('Date'[Start of Financial Month]), Date'[Date] = _selected_date)

Next step:
Count =
VAR _fin_date = [Fin Date 01]
RETURN
Calculate(countrows('Table A'), 'TableA'[Date]  >= __fin_date)

Community Champion

Try change in the last line to date[date] instead of table[date]. You should use the columns from the date dimension and not the ones in the fact table on these places in the expression

Helper I

That worked, thanks again.

Community Champion

my pleasure 🙂
Please don't forget to accept the previous message as a solution for community visibility.
Do check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. Please give it a thumbs up over there if you liked it 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543