Hi All,
The following code creates a table, based on a table with a column of dates. What I can't figure out is how to add an Index column, so it can then be used to order the Visual Date column. I can't use RANKX as it creates sequential column, but based on dates. Any ideas or pointers would be very much appreciated as I'm totally stuck. Thanks.
Dynamic Date Selections =
UNION(
ADDCOLUMNS(CALENDAR(MIN('Calendar'[Date]),MAX('Calendar'[Date])),
"Visual Date", UPPER(FORMAT([Date],"mmm")) & " " & RIGHT(
IF(MONTH([Date]) >= 4, FORMAT(YEAR([Date]), "00"), FORMAT(YEAR([Date])-1, "00")),2) & "/" & RIGHT(IF(MONTH([Date]) >= 4, FORMAT(YEAR([Date]), "00"), FORMAT(YEAR([Date])-1, "00")),2)+1,
"Type", "Reporting Month Year",
"Order", 1),
ADDCOLUMNS(CALENDAR(MIN('Calendar'[Date]),MAX('Calendar'[Date])),
"Visual Date", if( MONTH([Date]) >=4, YEAR([Date]), YEAR([Date])-1) & "-" &if( MONTH([Date]) >=4, YEAR([Date]), YEAR([Date])-1)+1,
"Type", "Reporting Year",
"Order", 2
))
The following code creates a table, based on a table with a column of dates. What I can't figure out is how to add an Index column, so it can then be used to order the Visual Date column. I can't use RANKX as it creates sequential column, but based on dates. Any ideas or pointers would be very much appreciated as I'm totally stuck. Thanks.
Dynamic Date Selections =
UNION(
ADDCOLUMNS(CALENDAR(MIN('Calendar'[Date]),MAX('Calendar'[Date])),
"Visual Date", UPPER(FORMAT([Date],"mmm")) & " " & RIGHT(
IF(MONTH([Date]) >= 4, FORMAT(YEAR([Date]), "00"), FORMAT(YEAR([Date])-1, "00")),2) & "/" & RIGHT(IF(MONTH([Date]) >= 4, FORMAT(YEAR([Date]), "00"), FORMAT(YEAR([Date])-1, "00")),2)+1,
"Type", "Reporting Month Year",
"Order", 1),
ADDCOLUMNS(CALENDAR(MIN('Calendar'[Date]),MAX('Calendar'[Date])),
"Visual Date", if( MONTH([Date]) >=4, YEAR([Date]), YEAR([Date])-1) & "-" &if( MONTH([Date]) >=4, YEAR([Date]), YEAR([Date])-1)+1,
"Type", "Reporting Year",
"Order", 2
))