topic Re: Adding description to time measure in DAX Commands and Tips

Adding description to time measure

njxfoster — Tue, 13 Dec 2022 22:59:33 GMT

Hi Guys

I have set a weekending measure to show the week starting date vs. the week ending date

For X-axis, is there a way I can change the display from showing "November 20" to " Week of 11/20"?

Thanks in advance!

Re: Adding description to time measure

njxfoster — Tue, 13 Dec 2022 22:59:05 GMT

Hi Guys

I have set a weekending measure to show the week starting date vs. the week ending date

For X-axis, is there a way I can change the display from showing "November 20" to " Week of 11/20"?

Thanks in advance!

Re: Adding description to time measure

Anonymous — Tue, 13 Dec 2022 23:19:47 GMT

Yes but I don't believe it would ever be recommended.

The formula you have utilises data type Date or Date/Time. This maintains chronological settings.

You could create a custom column using the following code:

WeekOf = "Week Of " & FORMAT([WeekEnding], "mm/dd")

This introduces a host of issues though relating to data control due to a change in data types. To put "week of" into a column means your data type changes to Text. You will lose chronological control and your columns will be shown alphabetically (or alphanumerically based on the digit assignment). The only real way to control this would then be to create a separate table that has a ranking column next to it so you could list them in the order you want to.

If this is due to user preference then education/expectations management is key.

Re: Adding description to time measure

Anonymous — Wed, 14 Dec 2022 01:38:43 GMT

Hi njxfoster ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Week = WEEKNUM('Table'[Date],2)

mindate = var _weeknum=WEEKNUM('Table'[Date],2) var _weekday=WEEKDAY('Table'[Date],2) return MINX(FILTER(ALL('Table'),'Table'[Week]=EARLIER('Table'[Week])),[Date])

Flag = "Week of" &" "& MONTH('Table'[mindate])&"/"&DAY('Table'[mindate])

2. Add Column – Index Column – From 1.

3. Create calculated table.

Table2 = SUMMARIZE('Table','Table'[Flag],"Index",MINX(FILTER(ALL('Table'),'Table'[Flag]=EARLIER('Table'[Flag])),[Index]))

4. Join the relationship between two tables.

5. Column[Flag] – Column tools – Sort by column -- [Index] .

6. Result:

Best Regards,

Liu Yang

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