Forum Discussion
Month filter
Hello,
I can't figure out how to arrange a filter that filters a table based on the current month. For the example table below I would like to use the following DAX measure:
Filter = From monthnr <= Month(Today () ) AND Until monthnr >= Month(Today () )
But this will not work for article C. If the current monthnr is 1, 2 or 3, article C should be returned but it will not because the 'From monthnr' argument is not valid. How can I solve this?
| Article | From month | From monthnr | Until month | Until monthnr |
| A | January | 1 | April | 4 |
| B | July | 7 | August | 8 |
| C | November | 11 | March | 3 |
Hopefully you have an idea. Thank you in advance!
Raymond
Thanks for the assist, but this still leaves the problem with article C for months 11 and 12 as they will result in "No" due to the last argument for MONTH (TODAY () ) <= [Until Month].
I think I have found a solution by using the month numbers to create a date with new columns:From period =
IF(
[From month] > [Until month]
&& [From month] > MONTH( TODAY() ),
DATE(
YEAR( TODAY() ) -1,
[From month],
1
),
DATE(
YEAR( TODAY() ),
[From month],
1
)
)
Until period =
VAR MonthNr = [Until month]
VAR Year =
IF(
[From month] > [Until month]
&& [From month] <= MONTH( TODAY() ),
YEAR( TODAY() ) +1,
YEAR( TODAY() )
)
VAR DayNr =
CALCULATE(
MAX( DateTable[Day of month] ),
DateTable[Month Number] = MonthNr,
DateTable[Year] = Year
)RETURN
IF(
[From month] > [Until month]
&& [From month] <= MONTH( TODAY() ),
DATE(
YEAR( TODAY() ) +1,
[Until month],
DayNr
),
DATE(
YEAR( TODAY() ),
[Until month],
DayNr
)
)
5 Replies
You can just add another condition in case the from month is large than the until month.
Column = IF([From month]>[Until month] && MONTH(TODAY())<=[Until month],"Yes",IF(MONTH(TODAY())>=[From month] && MONTH(TODAY())<=[Until month],"Yes", "No"))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.raymondvis ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.See if time intelligence can help
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKAWel basically the table remains the same, only the month names are not included. The articles are to be ordered within a certain period which is indicated in month numbers. But these periods are not year based, they are month based. Therefore I cannot use a date like November 2020 until May 2021, but only November until May.
Below you find the sample table once again.
The column ( I only added this column as an example):
Current month = MONTH( TODAY() )
The column: Is it in period =
VAR CurrentMonth = MONTH( TODAY() )RETURN
IF(
Blad2[From month] <= CurrentMonth
&& Blad2[Until month] >= CurrentMonth,
"Yes",
"No"
)Article From month Until month Is it in period Current month A 1 4 Yes 4 B 7 8 No 4 C 11 5 No 4 D 1 5 Yes 4 D 11 12 No 4
Article C should be included as a Yes, because month 4 is in between the period month 11 to month 5. It is not an option to include the data like Article D, because then I would be adding 2 lines instead of 1.Any thoughts on how to fix this?
You can only add another condition in case the larger month is large than the month so far.
Column - IF([From month]>[Until month] & & MONTH (TODAY())<-[Until the month],"Yes",IF(MONTH(TODAY())>-[From month] & & MONTH (TODAY())<-[Until month],"Yes", "No"))Paul Zheng _ Community Support Team
If this post helps, please accept it as the solution to help other members find it faster.Thanks for the assist, but this still leaves the problem with article C for months 11 and 12 as they will result in "No" due to the last argument for MONTH (TODAY () ) <= [Until Month].
I think I have found a solution by using the month numbers to create a date with new columns:From period =
IF(
[From month] > [Until month]
&& [From month] > MONTH( TODAY() ),
DATE(
YEAR( TODAY() ) -1,
[From month],
1
),
DATE(
YEAR( TODAY() ),
[From month],
1
)
)
Until period =
VAR MonthNr = [Until month]
VAR Year =
IF(
[From month] > [Until month]
&& [From month] <= MONTH( TODAY() ),
YEAR( TODAY() ) +1,
YEAR( TODAY() )
)
VAR DayNr =
CALCULATE(
MAX( DateTable[Day of month] ),
DateTable[Month Number] = MonthNr,
DateTable[Year] = Year
)RETURN
IF(
[From month] > [Until month]
&& [From month] <= MONTH( TODAY() ),
DATE(
YEAR( TODAY() ) +1,
[Until month],
DayNr
),
DATE(
YEAR( TODAY() ),
[Until month],
DayNr
)
)
