topic Re: DAX formula that parses whether a date is in the current month, before the month, or after the curre in DAX Commands and Tips

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

osvaldocordeiro — Thu, 30 Mar 2023 14:37:47 GMT

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!

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

Anonymous — Thu, 30 Mar 2023 14:52:52 GMT

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.

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

Ajendra — Thu, 30 Mar 2023 17:45:26 GMT

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

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

osvaldocordeiro — Fri, 31 Mar 2023 13:29:50 GMT

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!

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

osvaldocordeiro — Fri, 31 Mar 2023 13:42:40 GMT

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!

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

osvaldocordeiro — Fri, 31 Mar 2023 13:36:28 GMT

Sorry, EylesIT for not putting your name on it.

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

Anonymous — Fri, 31 Mar 2023 13:40:33 GMT

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

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

osvaldocordeiro — Mon, 10 Apr 2023 13:59:33 GMT

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

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

osvaldocordeiro — Mon, 10 Apr 2023 14:00:46 GMT

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: