1

I have 2 columns that includes 2 different dates.

What i'm trying to do is to create a calculated column that determines whether column "governance" is <= TODAY and the "contract issue" column is between the start and end of the next month( only March dates). with either "yes" or "no" in the if statement.

i've tried the following measure:

IF ( 'Table'[Governance] <= TODAY() && 'Table'[Contract Issue] <= EOMONTH ( Today(), 1 ), "yes", "No" )

but that returns all contract issues before end of the next month, what do i use to show the contract issue date between start of the next month and end of the next month?

Sample table below

Governance Contract Issue
14/01/2022 04/03/2022
04/02/2022 11/03/2022

Much thanks in advance

Mido
  • 11
  • 1
  • 4

1 Answers1

1

It seems you just need to compare the date to the start of the month.

mymeasure = IF (
    'Table'[Governance] <= TODAY ()
        && 'Table'[Contract Issue] <= EOMONTH ( TODAY (), 1 )
        && 'Table'[Contract Issue]
            >= DATE ( YEAR ( 'Table'[today] )
                + IF ( MONTH ( 'Table'[today] ) = 12, 1, 0 ), MONTH ( 'Table'[today] )
                + IF ( MONTH ( 'Table'[today] ) = 12, -11, 1 ), 1 ),
    "yes",
    "No"
)

You'll need a "today" column, too. Do that in Power Query using M:

Date.From(DateTime.LocalNow())
dougp
  • 2,810
  • 1
  • 8
  • 31