DAX formula that parses whether a date is in the current month, before the month, or after the curre

[osvaldocordeiro]

Good morning guys, I need help in the following sense:

If the date is in the current month, it will show a yellow light, if the date is before the month of the current month, it will show a red light and if the date is after the current month, it will show an orange light.

I thank you all!

[Anonymous · Accepted solution]

@osvaldocordeiro , no problem. Do you know you can edit your post? Click on the three vertical dots at the top-right of the post and select Edit Reply

[osvaldocordeiro]

Good morning EylesIT,

In addition to your help, we managed to solve the challenge. Thanks a lot for the help.

See how it turned out:

Indicator = VAR DATA_X = DATE(YEAR(TODAY()), MONTH(TODAY()), "01") RETURN IF('Portfolio Planning 2023'[Planned Start Period  ].[MonthNo] == MONTH(TODAY()) && YEAR(TODAY()) == 'Portfolio Planning 2023'[Planned Start Period  ].[Year], 0, IF('Portfolio Planning 2023'[Planned Start Period  ]< DATA_X, -1,1))

[Ajendra]

Status =

    SWITCH(

        TRUE(),

            MAX(FactTable[Date]) >= EOMONTH(TODAY(),-1)+1 &&

                MAX(FactTable[Date]) <=EOMONTH(TODAY(),0), "Yellow",

                MAX(FactTable[Date])<EOMONTH(TODAY(),-1)+1,"#FFCCCB",

                MAX(FactTable[Date])>EOMONTH(TODAY(),-1)+1,"#FFD580")

Try this once!

Use this measure in Conditional formatting >> Find value option

[osvaldocordeiro]

Good morning Ajendra, first I want to thank you for your help. Thank you very much!

I am in need of a calculated column, as the measure did not work in my table. Then, I changed from measure to calculated column, but the result is not working. Look:

Thank you in advance if you can help me complete this challenge!

[osvaldocordeiro · Accepted solution]

Good morning Ajendra,

In addition to your help, we managed to solve the challenge. Thanks a lot for the help.

See how it turned out:

Indicator = VAR DATA_X = DATE(YEAR(TODAY()), MONTH(TODAY()), "01") RETURN IF('Portfolio Planning 2023'[Planned Start Period  ].[MonthNo] == MONTH(TODAY()) && YEAR(TODAY()) == 'Portfolio Planning 2023'[Planned Start Period  ].[Year], 0, IF('Portfolio Planning 2023'[Planned Start Period  ]< DATA_X, -1,1))

[Anonymous]

@osvaldocordeiro , this is one way. Create a DAX measure 

KPIColour = 
    SWITCH(TRUE()
        ,DATEDIFF(SELECTEDVALUE('Period'[date_from]), NOW(), MONTH) = 0, "Current month - Yellow"
        ,DATEDIFF(SELECTEDVALUE('Period'[date_from]), NOW(), MONTH) >= 0, "Before current month - Red"
        ,DATEDIFF(SELECTEDVALUE('Period'[date_from]), NOW(), MONTH) <= 0, "After current month - Orange"
    )

This uses teh date field [date_from] in my time table called 'period'. When I create a visual and place the fields [date_from] and the measure KPIColour, this is the result:

Hope this helps.

[osvaldocordeiro]

Good morning EylesIT, first I want to thank you for your help. Thank you very much!

I am in need of a calculated column, as the measure did not work in my table. Then, I changed from measure to calculated column, but the result is not working. Look:

Thank you in advance if you can help me complete this challenge!

[osvaldocordeiro]

Sorry, EylesIT for not putting your name on it.

[Anonymous · Accepted solution]

@osvaldocordeiro , no problem. Do you know you can edit your post? Click on the three vertical dots at the top-right of the post and select Edit Reply