Forum Discussion

Angel's avatar
Angel
Icon for Resolver III rankResolver III
9 years ago
Solved

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,

 

 

CALCULATE
DAX
Filter
measure
previous week
snapshot
  • kaushikd's avatar
    kaushikd
    9 years ago

    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=eyJrIjoiOGRjNDYyMmUtOWFhMy00ZGE3LThjMjQtNTA2NTFhZTE5ODViIiwidCI6ImJmNzM2NTcyLTk5OTgtNDM3NC05YjA4LTU3MzQ3NGE3MzNhYSIsImMiOjEwfQ%3D%3D

     

    First of all you need to model up your data

     

    Modelling Dataset

     

    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

6 Replies

  • kaushikd's avatar
    kaushikd
    Icon for Resolver II rankResolver II
    9 years ago

    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=eyJrIjoiOGRjNDYyMmUtOWFhMy00ZGE3LThjMjQtNTA2NTFhZTE5ODViIiwidCI6ImJmNzM2NTcyLTk5OTgtNDM3NC05YjA4LTU3MzQ3NGE3MzNhYSIsImMiOjEwfQ%3D%3D

     

    First of all you need to model up your data

     

    Modelling Dataset

     

    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

    • Angel's avatar
      Angel
      Icon for Resolver III rankResolver III
      9 years ago

      Hi, kaushikd

       

      Thanks for your answer. It's nearly works.. ;)

       

      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,

       

      • Angel's avatar
        Angel
        Icon for Resolver III rankResolver III
        9 years ago

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

         

         

        Many thanks,

         

         

    • accortdr's avatar
      accortdr
      Icon for Helper I rankHelper I
      7 years ago

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