Forum Discussion

raymondvis's avatar
raymondvis
Frequent Visitor
5 years ago
Solved

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 monthn...
Need Help
  • raymondvis's avatar
    raymondvis
    5 years ago

    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
    )
    )

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

@raymondvis

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"))
V-pazhen-msft_0-1619576346121.png

Paul Zheng _ Community Support Team
If this post helps, please accept it as the solution to help other members find it faster.

raymondvis's avatar
raymondvis
Frequent Visitor
5 years ago

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
)
)