Transaction level date to Rolling Time Summary

[MatthewMcL]

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

[Anonymous]

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

[MatthewMcL]

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.

[Seward12533]

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. 

[MatthewMcL]

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.

[Anonymous · Accepted solution]

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)

[MatthewMcL]

This is exactly what I was looking for. Thank you!

[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])
)

[MatthewMcL]

Hmm, I am still getting blank values after creating the Date Dimension table and linking it to the Date column in my dataset.

My Date Dimension table is just unique dates between the minimum date and maximum date within my original dataset.

2daysum = CALCULATE(
        SUM(Sheet1[Sold]),
       FILTER(ALL('DateTable'),'Sheet1'[Date]>=DateTable[Date]-2 && Sheet1[Date]<DateTable[Date]
))

[Seward12533]

Please check two thing

1. Date Table a conguous list of days (no missing days)
2. Both the DATE Key in the Date Table and the Date Column in the Fact Table are rormatted to Date Only and Not Date/Time

I tested the same formula on a data set I have and worked as expected