DAX Measure for Calculated Column containing Numbers and Text

[dyttam]

Hello, 

I am attempting to recreate a visual from Tableau that has Cost on a bar chart. The bar chart has 12 months of data but also has buckets for data with dates outside of the 12 months.

See image below:

The formula within Tableau is the following: 

So far, I have decided to create a calculated column with a switch function... and this is as far as I have gotten: 

SWITCH(

TRUE(),

'FactTable'[Expiry_Date] = BLANK(), "No Expiry Date Mentioned",

'FactTable'[Expiry_Date] < 0, "Already Expired",

'FactTable'[Expiry_Date] > 365, "Expire After 365 Days",
)

What I need help with is how to do the year-month DAX. I'm not sure where to go from here. 

[ghoshabhijeet]

@dyttam You can use the Year and Month function to get the values respectively and add them.

=YEAR(date column) & MONTH(date column)

or you can use 

=CONCATENATE(YEAR(date column), MONTH(date column))

[dyttam]

Hmm.. Not sure I am following. I've stripped my DAX down to the basic level and this is how it's functioning so far. 


I need the (blank) values to be put into two additional buckets. 

The first bucket would be "Expiry Date > 365 Days". 

What DAX would you use to grab all rows that would be an Expiry Date 365+?
I tried TODAY() + 365 but that made no sense (clearly). Perhaps I could use DATEADD[FactTable[Expiry_Date], 1, YEAR ?

The second bucket would be the actual date that fall within the next 365 days (in month and year). 
I'm stumped on how to write this measure. 

[ghoshabhijeet]

@dyttam  Could you please share your pbix file with some dummy data ?

[dyttam · Accepted solution]

I was able to figure out the measure by creating a Month Description Column and using DATEADD <Today+365. 

Expiry Date Dynamic =

SWITCH(

TRUE(),

AND('mdl Daily_Inventory_Expiration'[Expiry_Date] >= 'mdl Daily_Inventory_Expiration'[Today Date],

'mdl Daily_Inventory_Expiration'[Expiry_Date] <=(TODAY()+365)),

'mdl Daily_Inventory_Expiration'[Month Desc],

'mdl Daily_Inventory_Expiration'[Expiry_Date] = BLANK(), "No Expiry Date Mentioned",

'mdl Daily_Inventory_Expiration'[Expiry_Date] < TODAY(), "Already Expired",

'mdl Daily_Inventory_Expiration'[Expiry_Date] > (TODAY()+365), "Expire After 365 Days")

Month Desc = FORMAT('mdl Daily_Inventory_Expiration'[Expiry_Date],"YYYY-MM")

[v-kelly-msft]

Hi @dyttam ,

So your issue is solved,right?

If so ,could you pls mark the reply as answered to close it?

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!