## DAX Commands and Tips

Member
Posts: 100
Registered: ‎05-25-2016

# Sales amount previous week (snapshot)

Hi, everyone

I hope someone can help me

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

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.

Does anyone know how can I do that?.

Thanks,

Accepted Solutions
Member
Posts: 47
Registered: ‎03-07-2017

## Re: Sales amount previous week (snapshot)

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

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.

All Replies
Member
Posts: 47
Registered: ‎03-07-2017

## Re: Sales amount previous week (snapshot)

Hi

Member
Posts: 47
Registered: ‎03-07-2017

## Re: Sales amount previous week (snapshot)

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

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.

Member
Posts: 100
Registered: ‎05-25-2016

## Re: Sales amount previous week (snapshot)

Hi, @kaushikd

It works when I use WeekNumber in rows

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

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,

Member
Posts: 100
Registered: ‎05-25-2016

## Re: Sales amount previous week (snapshot)

Hi, @kaushikd

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

To calculate difference I have used phormula:

Many thanks,

Highlighted
Member
Posts: 100
Registered: ‎05-25-2016

## Re: Sales amount previous week (snapshot)

Hi, @kaushikd

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

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,