Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors