measure to calculate the difference from non-date columns

[kien79]

Hi Experts. I have the data as below which I'd like to create a measure to dynamically calculate the difference vs. the previous week. Once I add new data of the new week, the difference will be calculated automatically. Thing is the week is text format. Also, I would like to add a chart to show the differences between the current and previous week, in which it would be great if I can select and view top N categories with big changes. Can you help? Thank you

[audreygerred · Accepted solution]

Hi! Ideally, the data will be flattened so that each week is not a new column (it will have a date), but a new row; your semantic model will be in a star schema format with a date table the date from your fact will connect to the date in the dim table and the dim table will say the week number, then you can follow this article to help with the time intelligence measures to get your WoW. Week-Based Time Intelligence in DAX - SQLBI