topic Re: Dynamic Column Headings - Dates Over Time in DAX Commands and Tips

Dynamic Column Headings - Dates Over Time

cgallegos — Fri, 28 Jan 2022 19:23:26 GMT

Hi - I am wanting to have column headings that change as my dashboard is refreshed or updated.

For example, I'd like the below "current week" heading to say "Jan 14-20" as the values in that column are calculated for that date range. Is there a way to do this?

Thank you,

Re: Dynamic Column Headings - Dates Over Time

lbendlin — Sat, 29 Jan 2022 21:46:58 GMT

If by "dynamic" you mean "recalculated during every import mode dataset refresh" then yes. Otherwise please provide more details, some sample data would be nice too.

Re: Dynamic Column Headings - Dates Over Time

cgallegos — Mon, 31 Jan 2022 15:14:14 GMT

Essentially, I'd like to see week over week # of Hired Employees. The weeks runFriday-Thursday, though.
For example, if my raw data looks like something like this:

Hires	Region	Effective Date of Hire	Dashboard Refresh Date
1	Consumer Ret	1/14/2022	1/31/2022
1	Consumer Ret	1/14/2022	1/31/2022
1	Consumer Ret	1/14/2022	1/31/2022
1	Consumer Ret	1/14/2022	1/31/2022
1	Consumer Ret	1/14/2022	1/31/2022
1	Consumer Ret	1/20/2022	1/31/2022
1	Consumer Ret	1/21/2022	1/31/2022
1	Consumer Ret	1/21/2022	1/31/2022
1	Consumer Ret	1/27/2022	1/31/2022

And I'd like it summarized like this

	Hires
Region	Jan 14-20	Jan 21-27	YTD
Consumer Ret	6	3	9

Re: Dynamic Column Headings - Dates Over Time

lbendlin — Mon, 31 Jan 2022 15:22:25 GMT

What would be the expected column header for the next week? "Jan 28-Feb 3" ? You may be running out of screen real estate fast.

Re: Dynamic Column Headings - Dates Over Time

cgallegos — Mon, 31 Jan 2022 15:24:35 GMT

Hi,

Yes, next column would be Jan 28-Feb 3. I am only planning to show the last 4 weeks.

Re: Dynamic Column Headings - Dates Over Time

lbendlin — Mon, 31 Jan 2022 15:25:56 GMT

PLease add some more sample data so it covers that whole period.

Re: Dynamic Column Headings - Dates Over Time

cgallegos — Mon, 31 Jan 2022 15:36:34 GMT

Raw Data:

Hires	Region	Effective Date of Hire
1	Consumer Ret	1/14/2022
1	Consumer Ret	1/14/2022
1	Consumer Ret	1/14/2022
1	Consumer Ret	1/14/2022
1	Consumer Ret	1/14/2022
1	Consumer Ret	1/20/2022
1	Consumer Ret	1/21/2022
1	Consumer Ret	1/21/2022
1	Consumer Ret	1/27/2022
1	Consumer Ret	1/28/2022
1	Consumer Ret	1/28/2022
1	Consumer Ret	2/3/2022
1	Consumer Ret	2/3/2022
1	Consumer Ret	2/3/2022
1	Consumer Ret	2/4/2022
1	Consumer Ret	2/4/2022
1	Consumer Ret	2/10/2022
1	Consumer Ret	2/10/2022

Week Over Week - What I'd Like to Get to

Region	Jan 14-20	Jan 21-27	Jan 28-Feb 3	Feb 4 - Feb 10	YTD
Consumer Ret	6	3	5	4	18

Re: Dynamic Column Headings - Dates Over Time

cgallegos — Mon, 31 Jan 2022 16:41:45 GMT

Let me know if you need any additional data.

Re: Dynamic Column Headings - Dates Over Time

lbendlin — Mon, 31 Jan 2022 19:31:36 GMT

I initially implemented this as a DAX calculated column but then realized this would not work as it doesn't sort the result correctly. I reverted back to the standard method of supplying an external calendar table instead. See attached for both options.

Re: Dynamic Column Headings - Dates Over Time

cgallegos — Mon, 31 Jan 2022 21:34:38 GMT

Thank you!!