Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
AlexStott
New Member

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

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

 

 
DateBrandProductUnitsValue
01/01/2024Brand 1Product 135641600
02/01/2024Brand 1Product 149173
03/01/2024Brand 1Product 129042
04/01/2024Brand 1Product 1236361
05/01/2024Brand 1Product 1273484
06/01/2024Brand 1Product 1172240
07/01/2024Brand 1Product 1246224
08/01/2024Brand 1Product 1401232
09/01/2024Brand 1Product 1179374
10/01/2024Brand 1Product 1218184
11/01/2024Brand 1Product 1310
12/01/2024Brand 1Product 1113144
13/01/2024Brand 1Product 19121
14/01/2024Brand 1Product 1335388
15/01/2024Brand 1Product 1152324
16/01/2024Brand 1Product 1157343
17/01/2024Brand 1Product 1280178
18/01/2024Brand 1Product 17975
19/01/2024Brand 1Product 1203345
 

 

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;

 

 

AlexStott_6-1708723864386.png

 

 

 

 

 

 

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

 

 

AlexStott_4-1708723831647.png

 

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.

3 REPLIES 3
hansontm
Regular Visitor

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)

hansontm_0-1708726319651.png

 

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: 

hansontm_1-1708726416708.png

 

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

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.