Forum Discussion
Transaction level date to Rolling Time Summary
Hello,
I am very new to PowerBI, DAX and M Query but I am very familiar with general SQL Queries, Excel and general programming.
I’ve been asked to develop something in powerbi and I’ve been hitting a huge wall on how to even begin the process.
I have a table with various dimensions but generally it looks like this. I have multiple items being sold every day. Each day has an amount sold of that item.
Item Name | Sold | Date |
Item 1 | 2 | 7/1/2018 |
Item 2 | 3 | 7/1/2018 |
Item 3 | 2 | 7/1/2018 |
Item 4 | 1 | 7/1/2018 |
Item 5 | 2 | 7/1/2018 |
Item 6 | 3 | 7/1/2018 |
Item 7 | 2 | 7/1/2018 |
Item 8 | 1 | 7/1/2018 |
Item 1 | 1 | 7/2/2018 |
Item 2 | 1 | 7/2/2018 |
Item 3 | 2 | 7/2/2018 |
Item 4 | 3 | 7/2/2018 |
Item 5 | 3 | 7/2/2018 |
Item 6 | 3 | 7/2/2018 |
Item 7 | 2 | 7/2/2018 |
Item 8 | 2 | 7/2/2018 |
What I want to know is the amount that was sold for each item over a specific date range. In this case, I want to see how many units of each item was sold over the previous 3 weeks or 21 days. This would work as a rolling amount for each unique day.
I imagine it would look similar to this
Item Name | Date | 4 Week Sum |
Item 1 | 7/1/2018 | 20 |
Item 2 | 7/1/2018 | 21 |
Item 3 | 7/1/2018 | 20 |
Item 4 | 7/1/2018 | 15 |
Item 1 | 7/2/2018 | 25 |
Item 2 | 7/2/2018 | 28 |
Item 3 | 7/2/2018 | 30 |
Item 4 | 7/2/2018 | 15 |
My first question is do I need to create a new unique table that has the unique dates and unique items like the above example? And if so, do I need to do this in M or DAX? Is it possible to say Column1 = Unique(Dates) Column 2 = For Date in Column1(Unique(Item Name))
My next question is being able to calculate the 4 week sum column
From a logic perspective, I imagine the query looking similar to
4WeekSum = CALCULATE(SUM([sold), FILTER(sold by Date > Date - #duration(21,0,0,0)))
I understand that the first 21 days of records will return errors because there isn’t enough history.
I apologize if this question sounds pretty verbose. I am struggling to accomplish things that I would generally assume is simple but I can only understand it in Excel or as code.
Thanks,
Matt
Hmm, not sure if you want to get the following results.
If it is, first create a calcuated column and use the following DAX to add Rank Number
Rank = RANKX(Sold,Sold[Date],,ASC,Dense)
Then, create another calucated column and use the DAX below to get the cumulative sum in 2 days by item name
2Days_Sum = CALCULATE(SUM(Sold[Sold]),ALLEXCEPT(Sold,Sold[Item Name]),Sold[Rank]<=EARLIER(Sold[Rank])&&Sold[Rank]>EARLIER(Sold[Rank])-2)
9 Replies
Seward12533Solution Sage
You don't need to buid a separeate table but can do it with Time Inteligence. So you need to have a date table. And then have a Measure that calculates the last 7,21, 30 or whatever period you want to sum over.
Here is an example for the last 7 Days - https://community.powerbi.com/t5/Desktop/Measure-for-count-of-last-7-days/td-p/106692
you can just replace SUM vs the Distinct Count.
Sorry I did a poor job explaining my problem. I tried to follow the solution you linked too and I ran into the same problem I've been having.
I don't want to look at the latest time frame but at 4 days from the date within the row.
I've been trying to apply time intelligence methods to the query but I keep running into the same issue.
Here is an example of a two day rolling sum.
2daysum = CALCULATE(
SUM(Sheet1[Sold]),
FILTER(ALL('Sheet1'), 'Sheet1'[Date] >= Sheet1[Date]-2 && Sheet1[Date]<Sheet1[Date])
)So I want to sum everything between the date 2 days ago and the current date in the row.
When I do this I just recieve blank results for every row.
- Seward12533
Time Intelligence functions need date tables. For this work you need a date table that is linked to your data and then you compare the date in your table to date in the date table.
2daysum = CALCULATE(
SUM(Sheet1[Sold]),
FILTER(ALL('DateDim'), 'Sheet1'[Date] >= DateDim[Date]-2 && Sheet1[Date]<DateDim[Date])
)
Not sure if you want to get the following results.
Say you have a table like this
Create a measure with the following DAX, it can give you the latest 10 days total sold counting for the max date value in the table
latest4Days = SUMX(FILTER(Sold,Sold[Date]<=MAX(Sold[Date])&&Sold[Date]>MAX(Sold[Date])-4),Sold[Sold])
If you want to get the last 4 days total sold counting from today, you can try the following DAX
Last4Days = SUMX(FILTER(Sold,Sold[Date]<=TODAY()&&Sold[Date]>TODAY()-4),Sold[Sold])
I apologize, I did a poor job explaining my question in that first post.
I want the sum to be 4 days from the rows date value.
So if my data covered the month of July. This particular rows date is 7-12-2018, I would want to sum all of the values from 7-8-2018 through 7-12-2018.
I would anticipate the values to change for every row since its a rolling sum of the previous 4 days. Since the test dataset that I am trying to use for this logic is self generated, I was keeping it small and trying to do it on two days.
2daysum = SUMX(FILTER(Sheet1,Sheet1[DATE] >= Sheet1[Date]-2 && Sheet1[Date]<=Sheet1[Date]),Sheet1[Sold])
But all I am getting is the same value for every row.
