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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Fixed Date, Dynamic Year - Date Filtering Woes

Hello!

 

It's me again with another date conundrum.

 

What I want to do is filter a range of data based on its date.  For example, I want to filter out anything from 1st April of the previous year.  So for 2020 that would be 1st April 2019 and for 2021, this would be 1st April 2020.

 

I can easily do this with a fixed date, but I have struggled to make this dynamic.

 

I have tried the following but get an error about not passing through a date value: -

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= Date.From(Date.AddMonths(Date.StartOfYear(DateTime.LocalNow)),-9))

 

In my mind, I'm asking it to filter out anything that is less than equal to the start of today's year minus 9 months.  E.g. 1st April 2019.

 

If anyone can shed some light on this for me, I would be much obliged.

1 ACCEPTED SOLUTION

@Anonymous - Try ditching your Date.From, I don't see why you need that. Also, you need () on your .LocalNow

 

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= Date.AddMonths(Date.StartOfYear(DateTime.LocalNow())),-9)



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Exact error message: -

 

Expression.Error: The Date value must contain the Date component.
Details:
[Function]

@Anonymous - Try ditching your Date.From, I don't see why you need that. Also, you need () on your .LocalNow

 

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= Date.AddMonths(Date.StartOfYear(DateTime.LocalNow())),-9)



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks, @Greg_Deckler - I always struggle with the date/datetime/datetimezone fields!

 

I've tried what you have suggested but now get: -

Expression.Error: 3 arguments were passed to a function which expects 2.
Details:
Pattern=
Arguments=[List]

 

Then I realised there were not enough brackets (or not in the right place) and get: -

 

Expression.Error: We cannot apply operator < to types DateTime and Date.
Details:
Operator=<
Left=01/10/2019 00:00:00
Right=21/08/2019

Anonymous
Not applicable

Ah I may have cracked it...

 

#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column1", each [Date] >= Date.From(Date.AddMonths(Date.StartOfYear(DateTime.LocalNow()),-9)))

 

Converting datetime to date with the Date.From - then matches the "Date" format in [Date]

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.