Date filter in a measure is not working!?

[Ben81]

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

[SpartaBI · Accepted solution]

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

      

Showcase Report – Contoso By SpartaBI

[SpartaBI · Accepted solution]

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

      

Showcase Report – Contoso By SpartaBI

[Ben81 · Accepted solution]

Worked like a charm, thanks! 🙂

[SpartaBI]

@Ben81 

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

[Ben81]

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 😞

[SpartaBI]

@Ben81 not sure I understand.

Do you want to do a quick zoom call?

[Ben81]

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)

[SpartaBI]

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

[Ben81]

That worked, thanks again.

[SpartaBI]

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