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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Angel
Resolver III
Resolver III

Sales amount previous week (snapshot)

Hi, everyone

 

I hope someone can help me

 

I include the image to explain what I want to do.Captura1.JPG

I would like to show how sales amount have been increased every month per week. To do that, I have tried to create a measure with this phormula.

      Sales amount last week = CALCULATE(SUM('Sales'[Sales amount]);FILTER(DimTable;DimTable[WeekCount]-1))

 

However, when I include the measure in the table, the result is the same than Sales Amount current week.

 

Captura2.JPG

 

       Does anyone know how can I do that?.

 

Thanks,

 

 

1 ACCEPTED SOLUTION
kaushikd
Resolver II
Resolver II

Hi

I have used your sample data set and tried to find Sales amount for previous week

Please have a look into it.

 

https://app.powerbi.com/view?r=eyJrIjoiOGRjNDYyMmUtOWFhMy00ZGE3LThjMjQtNTA2NTFhZTE5ODViIiwidCI6ImJmN...

 

First of all you need to model up your data

 

Modelling Dataset

Capture1.PNG

 

Model your data into this format please check the Applied Steps for more information basicly Pivoting the salesid from column to row will help.If your data is in such format you can jump to the next step.

 

 

Create a Date Dimension

Go to Data View for modelling

Select Modelling

Choose New Table

fire the DAX to create date dimension

DateDim = CALENDAR (DATE (2016, 1, 1), DATE (2016, 12, 31))

Now Select New Column and fire DAX

WeekNumber = WEEKNUM('DateDim'[Date].[Date]) 

to create weeknumber column into your date dimension.

 

Relationship

Create a 1 to many Relationship Between your Date Dim Dataset and PBI on basis of Date Dim[Date]=PBI[SnapshotDate]

 

Create Measure

Right Click on the DateDim Dataset and choose New Measure and Fire this query

Sales Previous Week = CALCULATE(Sum(PBI[Sales]),
FILTER(All('DateDim'), 'DateDim'[WeekNumber] = MIN('DateDim'[WeekNumber])-1 ))

 

Now in Report View

Drop a slicer and put WeekNumber into the field

Now Drop a Chart and Put Sales and the Sales Previous Week into the value Field.

 

 

If this fulfills your requirement please like this post

View solution in original post

6 REPLIES 6
kaushikd
Resolver II
Resolver II

Hi

I have used your sample data set and tried to find Sales amount for previous week

Please have a look into it.

 

https://app.powerbi.com/view?r=eyJrIjoiOGRjNDYyMmUtOWFhMy00ZGE3LThjMjQtNTA2NTFhZTE5ODViIiwidCI6ImJmN...

 

First of all you need to model up your data

 

Modelling Dataset

Capture1.PNG

 

Model your data into this format please check the Applied Steps for more information basicly Pivoting the salesid from column to row will help.If your data is in such format you can jump to the next step.

 

 

Create a Date Dimension

Go to Data View for modelling

Select Modelling

Choose New Table

fire the DAX to create date dimension

DateDim = CALENDAR (DATE (2016, 1, 1), DATE (2016, 12, 31))

Now Select New Column and fire DAX

WeekNumber = WEEKNUM('DateDim'[Date].[Date]) 

to create weeknumber column into your date dimension.

 

Relationship

Create a 1 to many Relationship Between your Date Dim Dataset and PBI on basis of Date Dim[Date]=PBI[SnapshotDate]

 

Create Measure

Right Click on the DateDim Dataset and choose New Measure and Fire this query

Sales Previous Week = CALCULATE(Sum(PBI[Sales]),
FILTER(All('DateDim'), 'DateDim'[WeekNumber] = MIN('DateDim'[WeekNumber])-1 ))

 

Now in Report View

Drop a slicer and put WeekNumber into the field

Now Drop a Chart and Put Sales and the Sales Previous Week into the value Field.

 

 

If this fulfills your requirement please like this post

If you use the code provided, wouldn't the MIN(WeekNumber) be 1 which effectively zeros out the week number you are filtering on?

Sales Previous Week = CALCULATE(Sum(PBI[Sales]),
FILTER(All('DateDim'), 'DateDim'[WeekNumber] = MIN('DateDim'[WeekNumber])-1 )) 

 

 

Hi, @kaushikd

 

Thanks for your answer. It's nearly works.. 😉

 

It works when I use WeekNumber in rows

Captura1.JPG

However, when I include snapshot date in rows, data appears in different rows. ¿Is there any way to avoid it?.

 

Captura2.JPG

Anyway, the thing I want to do is create a measure that shows how sales amount have been increased per week regard to month sales.

 

Any tip?.

 

Thanks,

 

Hi, @kaushikd

 

I have already done it.

 

The key is using the field DimDate.Date instead of Sales.Snapshot date.

 

To calculate difference I have used phormula:

 

Increase sales = CALCULATE(SUM('Planning registrado lunes'[Sales amount]) - CALCULATE(SUM('Planning registrado lunes'[Sales amount]);DATEADD(Calendario[Date];-7;DAY))   )

 

 

Captura.JPG

Many thanks,

 

 

Hi, @kaushikd

 

The final thing I want to do is only shows sales where sales date > Last snapshot date

 

Captura.JPG

In this sample it has only show sales where sales date >= 06/06/2016 in every snapshot date.

 

I know user can use slicers, but it could be great without user interaction.

 

Thanks,

kaushikd
Resolver II
Resolver II

Hi

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors