Rolling Snapshot Line Chart

Anonymous 

Hopefully I am understanding you correctly.

You want to see a line chart for the last 13 weeks, but you want to see the X-axis data points grouped by week (rather than the day)?

If so, then you can take the following steps.

1) Add the Calculated Column "Week Date Starting Monday" to the Invoice Table.
   - NOTE: DATEADD had to be replaced by DATE because DATEADD will return blank rows if the date does not exist in the source column - not cool, but that's DAX.

2) In the new line chart visual X-axis value, replace [Transaction_Date] with the new calculated column [Week Date Starting Monday].  Now all the data will be grouped by week.
   - NOTE: You still need the "In Last 13 Weeks" filter on the page or visual as mentioned in the previous post.

My apologies if I misunderstood your question.

If this is not what you are looking for, could you provide a simple mock-up in Excel to help me understand better?

Regards,

Nathan 

Anonymous 

Hello Ian,

Foolish mistake on my part.  Very sorry about that.

There is nothing wrong with your data.  My limited data just accidentally worked because I have no dates in the first few days of any month.

My code is flat wrong because I'm subtracting: DAY - WEEKDAY. (Digit - Digit)

- DAY is just the number of the day in the month.  It has no month or year context.

- IF DAY = 1 (1st of the month) & I subtract any number THEN we'll get a negative DAY number, which cannot be a valid DAY of any month, and therefore throws the error.

To write the code correctly, we need to subtract WEEKDAY from the entire DATE.  In this way, the problem is resolved:

OLD CODE: 1 - 4 = -3 (A negative DAY can never be part of a valid date.)

NEW CODE 7/1/2022 - 3 = 6/27/2022 (This results in a valid date.)

Hopefully this explanation helps you to understand my logical error.

Regards,

Nathan