Time intelligence - Month and Year Issue

[Matho101]

Hi,

New user so apologies for the ignorence. 

I am looking at using time intelligence to calculate Sales performance for Year on Year (total) & also Month vs Month. 

Problem is my sales data doesn't have a date column i.e sales have been grouped per month and per year.  When I try convert these to a time/date format is does not allow me to.  So data type remains as 'Text' for Month and 'Whole Number' for Year.  This means my time analysis DAX formulation doesn't work. 

Any suggestions?

[v-yalanwu-msft · Accepted solution]

Hi, @Matho101 ;

You could create a column as follow:

Column = MONTH ( [MONTH] & " 1" )

The final output is shown below:

Or create a column :

date = DATE([YEAR], MONTH ( [MONTH] & " 1" ),1)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Samarth_18]

Hi @Matho101 ,

Try this:-

Date = 
var _month = SWITCH([month],"Jan",1,
"Feb",2,
"Mar",3,
"Apr",4,
"May",5,
"Jun",6,
"Jul",7,
"Aug",8,
"Sep",9,
"Oct",10,
"Nov",11,
"Dec",12)
return DATE([Year],_month,1)

Output:-

Thanks,

Samarth

[amitchandak]

@Matho101 , Create a new date column using the DAX

Date = datevalue("01-"&[Month] & "-"&[Year] )

Join this with a date table(join with date not month) and use time intelligence

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=6LUBbvcxtKA

[Matho101]

Hi @amitchandak 

Thanks, however when I add the dax you suggested I get this?