cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:
1.JPG

 

The formula within Tableau is the following: 
2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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. 

 

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

View solution in original post

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

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. 

3.JPG
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. 

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

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

View solution in original post

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!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors