Forum Discussion
DATEADD when not in date format
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
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 ) )
1 Reply
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 ) )