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.

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
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!
4 REPLIES 4
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!
Employee

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"``

Employee

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!

Employee

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

## Helpful resources

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors