cancel
Showing results for
Did you mean:
Regular Visitor

DAX Measure for Calculated Column containing Numbers and Text

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] > 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.

1 ACCEPTED SOLUTION
Regular Visitor

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")
5 REPLIES 5
Helper I

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

Regular Visitor

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.

Helper I

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

Regular Visitor

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")
Community Support

Hi @dyttam ,

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

Much appreciated.

Best Regards,
Kelly

Announcements

Happy New Year from Power BI

This is a must watch for a message from Power BI!