topic Re: Changing Static to Dynamic existing Dax in DAX Commands and Tips

Changing Static to Dynamic existing Dax

Anonymous — Thu, 02 Jun 2022 03:50:24 GMT

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 for eg
V5-May-2021, V6-June-2021 To V1 Jan-2022, and it goes so every month user will share the new version of data with the same schema.
The schema is following the same schema, so I am using the append option. But The problem is after appending every time with a new version I want to change my version static value with a new version name like t

so every time I want to change this measure manually with the new version
My requirement is instead of this static method how can I achieve this solution as dynamically
for eg (Instead of writing every time a new version name in this dax if I have another measure that contains an updated version so I can mention that here)
Note. this table has a column (version) and it consists of different versions of the table which appended

Re: Changing Static to Dynamic existing Dax

Whitewater100 — Wed, 01 Jun 2022 16:55:21 GMT

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

Re: Changing Static to Dynamic existing Dax

Anonymous — Wed, 01 Jun 2022 17:15:41 GMT

Hai Whitewater100 No this is not based on today's date.
I just want to create anaother measure and instead of static i want to add that dynamic measure there
Requriement every month user send new version sales table with same schema so i appening and calendar is already created
So i want to after appending new version and instead of typeing newversion as static i want my report automatically filter based on max version
Already sales table have version column that column consist of values from v5 may 2021 to v4 april 2022 and also i had created a version flag too
now i want to set this static measure as dynamic
please help me to achieve this

Re: Changing Static to Dynamic existing Dax

Whitewater100 — Wed, 01 Jun 2022 17:37:09 GMT

Hi Alan:

Please send example data representing your model. Need more to work with. Thanks!

Re: Changing Static to Dynamic existing Dax

Anonymous — Thu, 02 Jun 2022 03:51:41 GMT

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

Re: Changing Static to Dynamic existing Dax

Whitewater100 — Wed, 01 Jun 2022 20:47:00 GMT

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)

https://drive.google.com/file/d/10vh5QiDRX4WXMBybt3XrrvPEd8wMCY0i/view?usp=sharing

There are other ways via power query to do this but I'm showing in DAX. I hpe this solves your question.

Re: Changing Static to Dynamic existing Dax

v-xiaotang — Wed, 15 Jun 2022 06:58:24 GMT

Hi Anonymous

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.