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.
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hai Whitewater100  please help me with this 


This is the data model master table contains all dimension values and the sales table is a fact table

 

This is the fact table here I will append every version of data in this particular table, I have a data of V5- May -2021 to v4- April-2022

 

After I had created a version flag so based on this report will understand the max of version

 

And user want to see the report based on the latest version of data and they want to compare it with previous ones
Requirment is , After appedning every version table instead of write a static latest version(like which i marked in red)
I want to add dynamic latest version. And report should automatically  update with latest version.Even the new version of table appending
Hope it clear please reach out still you have any doubt

 

Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
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.