Rolling Snapshot Line Chart

[Anonymous]

I am just learning DAX, and am having trouble with a request at work. I have read some similar posts but still having trouble understanding it to make my own. The topic is creating a Rolling snapshot for the Dates. Unfortunately I cannot upload a sample file. However I will do my best to explain and show what my tables look like. 

I created a Calendar table with CALENDARAUTO(). I then have Month, MonthNum, Qtr, Week, Year columns as shown below: 

Month = FORMAT(DATE(1, [MonthNum], 1), "MMM")

MonthNum = MONTH('Calendar'[Date])

Qtr = "Qtr" & " " & QUARTER('Calendar'[Date])

Week = 1+ WEEKNUM('Calendar'[Date])- WEEKNUM(STARTOFMONTH('Calendar'[Date]))

Year = YEAR('Calendar'[Date])

My data is an a table named 'Invoice'. With columns showing:

Create_Time

Transaction_Date

Due_Date

Total_Amount

Balance

I currently have a Many to 1 relationship between 'Invoice'[Transaction_Date] and 'Calendar'[Date]

I need to create a line chart that only shows the sum of 'Invoice'[Total_Amount] per day for the last 13 weeks. This needs to shift with the current date automatically. 

I also need another line chart with the same data... but showing the average per week, for the last 13 months. This also needs to shift with the current date. 

[WinterMist · Accepted solution]

@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 

[WinterMist · Accepted solution]

@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

[Anonymous]

That works perfect. Thank you. 

[WinterMist]

Hello Ian,

So [Day] is an auto-created number which represents the day of the month.

It only gets created if "Auto date/time" option is enabled under:

File \ Options & Settings \ Options \

1) GLOBAL \ Data Load \ Time Intelligence

2) CURRENT FILE \ Data Load \ Time Intelligence

Alberto Ferrari (SQLBI) highly recommends that this be disabled due to its limitations, and says that it's much better to create our own date tables using the CALENDAR function.  

As soon as this option is enabled in the Current File, you will see a Date Hierarchy expand under the Dates table in the Fields pane.

By adding it to a table visual, you can see its values show the number of the day of the month.

I'm pretty sure [Day] should not be used here since the parameter needed is the entire Date.

Thanks for sharing the file.  I'll take a look.

Nathan

[WinterMist]

@Anonymous

Did you have a chance to try it on a modified PBIX with just the data from 'Invoice'[Transaction_Date]?

If it's still giving the same error, can you provide a link to the modified PBIX?

Regards,

Nathan

[Anonymous]

Yeah, I've been messing around with it all morning. I tried making a few new PBIX with the data in different formats and sources. Same issue though. 

If I added .[Day] the error went away, but the dates were not all correct. I don't even know what the .[Day] means lol. This is shown in the picture. 

Rolling_Week_Template 

Regards, 

Ian

[WinterMist]

@Anonymous

"An argument of function 'DATE' has the wrong data type or the result is too large or too small."

Interesting.  I've never seen this error before.

The main solution I found is here:

https://community.powerbi.com/t5/Desktop/An-Argument-of-Function-Date-has-wrong-data-type-or-result-is/m-p/723910

However, I'm not yet seeing how this can help us.

This may take some time.

If anyone else already sees a way to overcome this error, please reply.

Regards,

Nathan

P.S.  You mentioned in your original post that you could not share a sample file.  However, since the error is not occurring for me, it must be something specific in your data.  Is it possible create a new PBIX with only 1 table & 1 column?  Copy all the data values from 'Invoice'[Transaction_Date] & use that to create a new PBIX with only that table & column as the data.  Then attempt to re-create the Calculated Column in the new PBIX for [Week Date Starting Monday].  If the error persists in the 2nd PBIX, then share the link to that PBIX and I can research it further.

[WinterMist]

@Anonymous 

So glad that was what you were looking for!

As far as learning DAX, there are many resources....but the book from sqlbi.com is the best, no joke.

"The Definitive Guide to DAX - 2nd Edition" by Russo & Ferrari

https://www.sqlbi.com/books/the-definitive-guide-to-dax-2nd-edition/

(Read Chapters 4 & 5 multiple times & refer back to them regularly.)

Check out this link for a great list of other resources from @m3tr01d .

https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-learn-DAX-from-beginning-to-Advance/m-p/2586316#M74389

Regards,

Nathan

[WinterMist · Accepted solution]

@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]

I am getting an error for the Date function. I attempted to use DateAdd instead just to test it... and that did not work either. 

[Anonymous]

This is exactly what I was wanting. Thank you. Are there any specific resources that you would recommend I use to better understand DAX? Seems a lot of it is memorizing all the functions and pieceing them together. 

[WinterMist]

@Anonymous 

No problem.  Here is the way to do it by week starting Monday & ending Sunday.

Regards,

Nathan

[Anonymous]

Gotcha. So How would you integrate that into the Filter for the 13 week period? Looks like it would be very much different then the first way: 

In Last 13 Weeks =

IF(

    'Invoice'[Transaction_Date] > TODAY() -91   --IF Transaction Date > 13 Weeks Ago Today

        && 'Invoice'[Transaction_Date] < TODAY() +1,    --And Transaction Date < Tomorrow

        1,0

)

From my thinking... I would need to be able to identify Today, find Week Date from Today, and then somehow do a 13 Week Date back count. 

[WinterMist]

@Anonymous 

Additional Notes:

- Calculated Columns are only re-calculated on refresh.

- As long as you have the report set to auto-refresh daily, the rolling time-frame will function properly.

For example, I refreshed the report this morning, and can now see that 7/21/2022 is correctly included in both Line Charts for today.

Regards,

Nathan

[Anonymous]

Thank you, this is definitely a helpful starting point. I do have a few extra notes.

Doing it this way is rolling forward by day. What if I wanted it to only shift one business week at a time? Let's say I consider my week as Monday- Sunday. When Monday comes along, we want the chart to show the dates for that Monday all the way through Sunday. 

My next question is if I want to Sum by Week. And have the rolling shift Monthly. 

Regards, 
Ian

[WinterMist]

@Anonymous 

Hopefully this is helpful to you.

Regards,

Nathan

Once the calculated column is created, you simply use it as a filter on the desired visual (or on the entire page as shown here).