Dynamic column headings - based on todays date

[Naverie]

Hi,

I'm really hoping this is possible. 

In an SSRS report we were able to code the column headings so they are dynamic based on todays date. I'm wanting to do the same in a table (it can be a matrix if needed).

I've currently just renamed the columns but obviously this isn't going to work when the report is published.

This is how we did it in SSRS:

= "D " +
vbcrlf + format(today(),"ddd") +
vbcrlf + format(today(),"dd/MM")

and then:

= "D+1 " +
vbcrlf + format(dateadd("d",1,today()),"ddd") +
vbcrlf + format(dateadd("d",1,today()),"dd/MM")

etc

Thanks in advance

[Greg_Deckler]

@Naverie Only way I can think of is to have your table headers in a disconnected (maybe connected) table so that it recalculates at refresh. Then use these columns. So, for example:

Table = 
  DATATABLE(
    "Heading",STRING,
    {
      {"D" & FORMAT(TODAY(),"dd/MM")},
      {"D+1" & FORMAT(TODAY()+1,"dd/MM")},
      {"D+2" & FORMAT(TODAY()+2,"dd/MM")}
    }
  )

[Naverie]

@Greg_Deckler thank you for your response.

I've just tried that and am getting this error:

The tuple at index '1' from the table definition of the DATATABLE function does not have a constant expression in the column at index '1'.

I have pasted it into a measure exactly as it is. Is that correct?

Thanks

[Greg_Deckler]

@Naverie Oh yeah, DATATABLE... Try a straight table constructor:

Table 4 = 
    {
        "D" & " " & FORMAT(TODAY(),"dd/MM"),
        "D+1" & " " & FORMAT(TODAY()+1,"dd/MM"),
        "D+2" & " " & FORMAT(TODAY()+2,"dd/MM")
    }

[Naverie]

@Greg_Deckler 

I am having a right mare with this 😂

Am now getting this and the table not loading?

A table with multiple values was supplied where a single value was expected

[Greg_Deckler]

@Naverie The formula is for a table. So you need to go to Modeling tab, click New Table and then enter the formula. This will not work for a column or measure.

[Naverie]

@Greg_Deckler so I was having a proper thick moment then lol!

That's now pulled through the values but in one column.

I'm assuming the way round this is to have a table for each of the ones I want?

[Greg_Deckler]

@Naverie Oh, hmm, I was thinking a matrix visual for some reason. So, am I to understand that you are using this in a table visualization? 

[Naverie]

This is the whole table. Ideally it needs to stay as a table as this is how they now view the data.