cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Regular Visitor

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

Community Support Team

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

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

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

Thank you

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 38 members 995 guests
Recent signins: