Switch Statement for a Calader to find last 3 months

[amaleranda]

Hi DAX Gurus,

I have a requirment to instert a tag for a last three month period into a continues date tabel. It works fine for last two months but it seems to have issue when I try to get the thrid month. below is my formula. I can see both 1 and 2 in the date tabel but I cannot see 3 in my date table column.

CurrentMonth1 = 

SWITCH(

TRUE(),

YEAR([Date]) = YEAR(TODAY()) && MONTH([Date]) = MONTH(TODAY())-1,1,

YEAR([Date]) = YEAR(TODAY()) && MONTH([Date]) = MONTH(TODAY())-2,2,

YEAR([Date]) = YEAR(TODAY()) && MONTH([Date]) = MONTH(TODAY())-3,3,

100

),

[amitchandak]

Try like

CurrentMonth1 = 
SWITCH(
TRUE(),
format([Date],"YYYYMM") = format(Date(YEAR(TODAY()) , MONTH([Date])-1,1),"YYYYMM") ,1,
format([Date],"YYYYMM") = format(Date(YEAR(TODAY()) , MONTH([Date])-2,1),"YYYYMM") ,2,
format([Date],"YYYYMM") = format(Date(YEAR(TODAY()) , MONTH([Date])-3,1),"YYYYMM") ,3,
100
)

[amaleranda]

@amitchandak ,

it does not seem to be working, 

Thanks anyway

[amitchandak]

@amaleranda , can you share the error

[az38]

@amaleranda 

try my suggestion above

[az38]

Hi @amaleranda 

the issue in the Year. now is 3rd month and there is no date with month number 0

I think you ned to use DATEDIFF function:

CurrentMonth1 = 
var _dDiff = DATEDIFF([Date], TODAY(), MONTH())
RETURN 
IF(_dDiff > 0 && _dDiff < 4, _dDiff, 100)

[amaleranda]

hi @az38 ,

excalty, that is the issue, but I just copied your code into the calander, it didnt seem to work.

I am pretty sure there is more to this code as you understand the issue. can you kindly put whole code in here for me.

Thanks.

[az38]

@amaleranda 

I think its exactly what you need.

Please, show example of data in your calendar table

Do you create a column or measure? this statement for column

[amaleranda]

@az38 ,

below is my code,

calander comes up blank when I do add this measure.

[amaleranda]

@az38 ,

When I am in current month I need to see last three months from month before. 

ie.  In March I should see feb 2020 as 1, jan 2020 as 2 and dec2019 as 3

if the curent month is Aug 2020

then I should see jul2020 as 1, june202 as 2 and may2020 as 3

[az38]

@amaleranda 

Now (in March) it works ok? If so, it will also work in appropriatedway in August

[az38 · Accepted solution]

Sorry, @amaleranda 

its my bad. try this

CurrentMonth1 = 
var _dDiff = DATEDIFF([Date], TODAY(), MONTH)
RETURN 
IF(_dDiff > 0 && _dDiff < 4, _dDiff, 100)

[amaleranda]

@az38 , You CHAMPION, thanks mate.