Required IsLast month and IsNext month for direct query in Power BI desktop

[Narukkp]

Hi Team,

I have few rows in my dataset with columns as Name and EndDate. For this data set required calculate columns like Last month and Next month (Suppose today date is 20/01/2019). My Data set is Direct Query mode. I required calculated columns only because they need to filter the data based on this flag columns.

Name	End Date	IsCurrentMonth	IsLastMonth	IsNextMonth
A	25/01/2019	1	0	0
B	18/12/2018	0	1	0
C	24/02/2019	0	0	1
D	20/01/2019	1	0	0

[v-lili6-msft · Accepted solution]

hi, @Narukkp

You could use these three formulas to add three calculate columns

new IsCurrentMonth = 
IF (
    YEAR ( Table1[End Date] ) * 100
        + MONTH ( Table1[End Date] )
        = YEAR ( TODAY () ) * 100
            + MONTH ( TODAY () ),
    1,
    0
)

new IsLastMonth = 
IF (
    YEAR ( Table1[End Date] ) * 100
        + MONTH ( Table1[End Date] )
        = IF (
            MONTH ( TODAY () ) = 1,
            ( YEAR ( TODAY () ) - 1 ) * 100 + 12,
            YEAR ( TODAY () ) * 100
                + MONTH ( TODAY () ) - 1
        ),
    1,
    0
)

new IsNextMonth = 
IF (
    YEAR ( Table1[End Date] ) * 100
        + MONTH ( Table1[End Date] )
        = IF (
            MONTH ( TODAY () ) = 12,
            ( YEAR ( TODAY () ) + 1 ) * 100 + 1,
            YEAR ( TODAY () ) * 100
                + MONTH ( TODAY () ) + 1
        ),
    1,
    0
)

Best Regards,

Lin

[Narukkp]

Thank you v-lili6-msft  for solution. It is working fine..

[Narukkp]

Any idea about this ???