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

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors