Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

  • ghoshabhijeet's avatar
    ghoshabhijeet
    Solution Supplier
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        ghoshabhijeet
        Solution Supplier
        5 years ago

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