cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ben81
Helper I
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
SpartaBI
Community Champion
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)


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

Worked like a charm, thanks! 🙂

View solution in original post

9 REPLIES 9
SpartaBI
Community Champion
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)


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Worked like a charm, thanks! 🙂

SpartaBI
Community Champion
Community Champion

@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

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
Community Champion
Community Champion

@Ben81 not sure I understand.

Do you want to do a quick zoom call?

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
Community Champion
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

That worked, thanks again.

SpartaBI
Community Champion
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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.