Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dyttam
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
ghoshabhijeet
Solution Supplier
Solution Supplier

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.