DATEADD when not in date format

[colverjustin]

Hi,

I have a table with date and sales. However the Date is in the format YYYWW - and its a whole number, not in date format

Date..................Sales

202201.............10

202202............30

202203............17

etc

I want a dax format to shift the numbers along a year, so that when create a table, it shows as

Date..................Sales

202301.............10

202302............30

202303............17

Is this poss?

Thanks,

Justin

[johnt75 · Accepted solution]

Create a proper date table, mark it as a date table, and add a column which has, for each date, a week in the YYYYWW format. You could do this with something like

Week number =
VALUE ( YEAR ( [Date] ) & WEEKNUM ( [Date] ) )

but you may need to play around with the WEEKNUM function to make sure that it works with your definition of when a week starts.

Create a many-to-many relationship from the date table to your existing fact table, with a single direction filter where Date filters your fact table, not the other way around. Make sure to use columns from your date table in any visuals or slicers, not the column from your fact table.

You will now be able to use the time intelligence functions, e.g.

Sales last week =
CALCULATE ( SUM ( 'Table'[Sales] ), DATEADD ( 'Date'[Date], -7, DAY ) )