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

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

Reply
kchoi00
Employee
Employee

Help Please: If and then with Power Query specific to date range

Hello!

I'd like to create a custom column in a Power Query that takes my Earning Date, and if it falls within a certain date range, then the custom column will show the earning period. I believe the formula would look something like this (although it isn't working):

IF([earningDate] >= #date(2020,7,1) & [earningDate] <= #date(2020,12,31) then "FY21H1"))

 

Thank you for your help!

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

@kchoi00 , the syntax for if statement in M code is like (case sensitive)

if ... then ... else ...

as to your issue, you might want to try

if [earningDate] >= #date(2020,7,1) and [earningDate] <= #date(2020,12,31) then "FY21H1" else "out of range"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

@kchoi00 , the syntax for if statement in M code is like (case sensitive)

if ... then ... else ...

as to your issue, you might want to try

if [earningDate] >= #date(2020,7,1) and [earningDate] <= #date(2020,12,31) then "FY21H1" else "out of range"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Thank you CNENFRNL. If I need to add another IF, would it look like this:

if [earningDate] >= #date(2020,7,1) and [earningDate] <= #date(2020,12,31) then "FY21H1" else "out of range"
if [earningDate] >= #date(2020,1,1) and [earningDate] <= #date(2020,6,30) then "FY20H1" else "out of range"

 

Apologies as this is my first time using this community support. @CNENFRNL Thank you. If I need to add another IF, how would I apply the 2nd IF statement, and possible 3 and 4 other IF statements. Thank you in advance!

Actually @CNENFRNL, I was able to figure it out with the else. Thank you again for your help!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors