cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Rolling Snapshot Line Chart

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. 

 

Date_Relationship.PNG

Calendar_Table.PNGInvoice_Table.PNG

2 ACCEPTED SOLUTIONS
WinterMist
Responsive Resident
Responsive Resident

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

WinterMist_0-1658427850811.png

 

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.

 

WinterMist_1-1658428054549.png

 

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 

View solution in original post

WinterMist
Responsive Resident
Responsive Resident

@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.)

 

WinterMist_0-1658788239775.png

 

WinterMist_1-1658788293284.png

 

Hopefully this explanation helps you to understand my logical error.

 

Regards,

Nathan

View solution in original post

15 REPLIES 15
WinterMist
Responsive Resident
Responsive Resident

@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.)

 

WinterMist_0-1658788239775.png

 

WinterMist_1-1658788293284.png

 

Hopefully this explanation helps you to understand my logical error.

 

Regards,

Nathan

Anonymous
Not applicable

That works perfect. Thank you. 

WinterMist
Responsive Resident
Responsive Resident

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.  

 

WinterMist_1-1658505006197.png

 

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.

 

WinterMist_0-1658504931579.png

 

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
Responsive Resident
Responsive Resident

@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
Not applicable

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 

Day_Added.PNG

Regards, 

Ian

WinterMist
Responsive Resident
Responsive Resident

@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
Responsive Resident
Responsive Resident

@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
Responsive Resident
Responsive Resident

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

WinterMist_0-1658427850811.png

 

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.

 

WinterMist_1-1658428054549.png

 

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
Not applicable

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. 

 

Week_Date_Starting_Monday.PNG

Anonymous
Not applicable

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
Responsive Resident
Responsive Resident

@Anonymous 

 

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

 

Regards,

Nathan

 

WinterMist_0-1658411207988.png

 

Anonymous
Not applicable

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
Responsive Resident
Responsive Resident

@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

 

WinterMist_0-1658409505191.png

 

Anonymous
Not applicable

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
Responsive Resident
Responsive Resident

@Anonymous 

 

Hopefully this is helpful to you.

 

Regards,

Nathan

 

WinterMist_0-1658354404180.png

 

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).

 

WinterMist_0-1658368246259.png

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors