cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Narukkp Regular Visitor
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.

 

NameEnd DateIsCurrentMonthIsLastMonthIsNextMonth
A25/01/2019100
B18/12/2018010
C24/02/2019001
D20/01/2019100
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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
Narukkp Regular Visitor
Regular Visitor

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

Any idea about this ???

Community Support Team
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.
Highlighted
Narukkp Regular Visitor
Regular Visitor

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

Thank you 

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 347 members 3,635 guests
Please welcome our newest community members: