Last 7/14/28 Data - Different values to unpivot on

[AlexStott]

Hey all,

Power BI n00b here!

I am trying to learn how to use Power BI, I am use to using Excel and SSRS to do reporting but want to dabble in Power BI.

I have data which looks like this from the SQL Server

Date	Brand	Product	Units	Value
01/01/2024	Brand 1	Product 1	356	41600
02/01/2024	Brand 1	Product 1	491	73
03/01/2024	Brand 1	Product 1	290	42
04/01/2024	Brand 1	Product 1	236	361
05/01/2024	Brand 1	Product 1	273	484
06/01/2024	Brand 1	Product 1	172	240
07/01/2024	Brand 1	Product 1	246	224
08/01/2024	Brand 1	Product 1	401	232
09/01/2024	Brand 1	Product 1	179	374
10/01/2024	Brand 1	Product 1	218	184
11/01/2024	Brand 1	Product 1	3	10
12/01/2024	Brand 1	Product 1	113	144
13/01/2024	Brand 1	Product 1	9	121
14/01/2024	Brand 1	Product 1	335	388
15/01/2024	Brand 1	Product 1	152	324
16/01/2024	Brand 1	Product 1	157	343
17/01/2024	Brand 1	Product 1	280	178
18/01/2024	Brand 1	Product 1	79	75
19/01/2024	Brand 1	Product 1	203	345

What I want to do with the Power BI report is

Select a Date as a slicer. This will then give me the data based on this date selected.

However, I want the output of the report to look like this;

I'm unsure how to do this best?

I tried to unpivot on the Units and Value columns so they become rows, then using a formula to get the totals I need. (I'm not using the slicer date selected yet, just trying things out with formulas)

e.g.

Day2Units = CALCULATE(SUMX(SampleData,SampleData[Value.1]),FILTER(SampleData,SampleData[Attribute]= "Units" && SampleData[Date].[Day] == 2))

But that is just creating a column in the data and not how I want it to look like.

I think I need to build a date hierarchy for the -1, -7,-14,-28 days I want. Is this correct?

The best I got was this; I don't want it by month though, I want it by a number of days/weeks going back. Plus I don't want to use a slicer to say start/end date, I want to select a start date then it "do the maths". 

But I'm just not sure where to go next with this! Any help would be great please.

I believe with the slicer I can turn interactions off and will allow me to use the slicer, not really sure how to point to that specific value I want though, that'll be the next thing.

Of course I am sure others have asked this but I just can't find anything online about it, so any help will be great please.

[hansontm]

Here are steps I did and the result:

1. I loaded your data and added a "week" column (could also do buckets of days like you mentioned 1-7,7-14,14-28, 28-31)

2. Created a matrix with week of month on columns and sum of units & sum of value on values.

3. Format visual > Values > options > switch values to rows

4. Result: 

[AlexStott]

Thanks for your help on this.

So this is closer to what I had to thanks for this.

The only expectation is that where you have 1,2,3 along the top it needs to be totaling it up.

e.g. Where it has 2, it needs to be the sum of 1+2, then 3 needs to be 1+2+3. 

I am trying to create columns which basically say; 

Sales Today 

Sales in the past week 

Sales in the past 2 weeks

Sales in the past 4 weeks

(There are others, like sales in the past 13 weeks etc, but I would assume I may be able to figure it out once I have the first set of columns). 

[hansontm]

They just released Visual Calculations in preview link which allows you to build a RUNNINGSUM.