Limit Gantt to Previous Month, Current Month, and Next Month

[rosie1931]

Hello,

I am trying to limit the time interval in a Gantt chart to the previous month, the current month, and the next month.

Here is what I have tried so far:

1. Created a formula to calculate the previous month and next month:

Previous Month: 

PrevMonth = PREVIOUSMONTH('Demo'[Todays Date])- Today's date calculates today's date

NexMonth= NEXTMONTH('Demo'[Todays Date])

When I try to put in both formulas at the start and end ranges, it will only accept using one. Otherwise, it will give say data is not available.

2. I put in these two formulas to extract the month and number:

MONTH(EOMONTH(TODAY(),-1))

MONTH(EOMONTH(TODAY(),1))

That extracted the correct digit. This comes out as a digit. My thought was to extract the year and concatenate as a date. However, how can that account for when there are divergent years (2023 and 2024) and re-converting into a date format?

Are either of these options workable? Thank you.

[rosie1931]

Thank you, Miguel. 

[MFelix · Accepted solution]

Hi @rosie1931 ,

The best option is to create a flag column where you identify if the values of the dates are within the previous, same and next month, then just filter out your gant based on that flag column.

[rosie1931]

Hi Miguel,

I created the columns in M query, which were not accessible for the visualization. Is that typically the case? 

[MFelix]

Hi @rosie1931 ,

What do you mean were not accessible for visualization?

[rosie1931]

I created the measure for the current month, previous month, and the quarter in M query. When I clicked to apply, those columns did not appear for me to use in the document's visualization.

[MFelix]

How is the code of that column in M?

[rosie1931]

I had opened the query editor and chose 'Custom Column.' I went back to the data view and then was able to make those formulas and it worked. Thank you for getting back to me.