Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Changing Static to Dynamic existing Dax

  Hai All, I am expecting a super solution for this requirement. The requirement is  Currently I am working on a sales dashboard and every month  this report is having different versions of tables...
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    Hi:

    I hope this does it for you. I'm attaching file (AdventureWorks).

    This file has three separate years of sales data in a folder, whicg is combined.

    Notice a calculated column called File Name. *I have kept the source file names in this table. It's easier if your file names contain what you want displayed in the end.

    CC in Sales Table >File Name =
    var right =
    RIGHT(AW_Sales[Source.Name],14)
     You can modify this example to match your file name. Which should be the column that says V5-May with no modifacations.  
    Your measure = MAX(Table[Source Name]) & "-" & FORMAT(TODAY(),"YY"))
    This result should be V5-MAY-22.  V5-MAY is already a name you have in your table.
    My Measure:
    Version = MAX(AW_Sales[File Name])
     
    Result:

     

    return
    LEFT(RIGHT(AW_Sales[Source.Name],14),10)
     
     
    There are other ways via power query to do this but I'm showing in DAX. I hpe this solves your question.
Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

Hi:

Here is an answer. It's based on todays date? So this will work if you append right at beginning of month. At this point I settled to show June as 06 vs just 6. See file here:

https://drive.google.com/file/d/1jDui2LrqmDHCvVdjXfjCPzHc-WgaK7HC/view?usp=sharing

If you would rather click on a Date from Slicer and get result we would change the TODAY() part with another variable 

UseDate = SELECTEDVALUE(Dates[Date]) if your Date Table is called "Dates" with Date field.

 

Version =
var MoNo = FORMAT(TODAY(),"MM")
var MonthShort = FORMAT(TODAY(),"MMM")
var Year2Space = FORMAT(TODAY(),"YY")
return
"V-" & MoNo & "-"& MonthShort & "-" & Year2Space