Forum Discussion

ApurvaKhatri's avatar
ApurvaKhatri
Helper III
8 years ago

Difference between previous week and this week data

Hello,   I have data as follows Date      Sales Oct 4 -  36 Oct 11 - 30 Oct 18 - 25 Oct 25 - 20     I need the difference between this week and previous week data i.e  Oct 25 - Oct 18 20 ...
Need Help
BILASolution's avatar
BILASolution
Solution Specialist
8 years ago

Hi ApurvaKhatri

 

The trick is get an index calculated column to sort Weeks. (You can use power query)

 

This is the result

 

 

 

And the measures in my case are...

 

Total Sales = SUM(Sales[Sales]) 
Total Sales PW = 
var thisweek = FIRSTNONBLANK(Sales[Index];1)
var prevweek = LOOKUPVALUE(Sales[Index];Sales[Index];thisweek-1)
return
CALCULATE([Total Sales];ALL(Sales[Date]);Sales[Index] = prevweek) 
Variance = [Total Sales] - [Total Sales PW]

I hope this helps

 

Regards

BILASolution

  • ApurvaKhatri's avatar
    ApurvaKhatri
    Helper III
    8 years ago

    Thanks for the solution. But in my case, sales is a measure so its not working

  • ApurvaKhatri's avatar
    ApurvaKhatri
    Helper III
    8 years ago

    Hello BILASolution

     

    In my case, Total Salea is a measure.

     

    Will this solution still work?

     

    Thanks,

    Apurva Khatri

    • BILASolution's avatar
      BILASolution
      Solution Specialist
      8 years ago

      Hi ApurvaKhatri

       

      Sure, In fact [Total Sales] is a measure in my case.

  • ApurvaKhatri's avatar
    ApurvaKhatri
    Helper III
    8 years ago

    Hi BILASolution and Ashish_Mathur

     

    I cannot share the exact data.

    But I would give you an idea hat I am trying to acheive

     

    Table : Sample

    Columns : PrincipalBalance (It is amount 1000, 24000, 32448, 767778) , Date (It contains everyday dates), Number(it has values negative, positive i.e 0.76, 0.4,-8.6, 75.0, 23, 5.4, 4.3 .... ), ID

     

    I have created calculated column :

    Day Of week = FORMAT ( Sample[Date], "dddd" ) - To extract the week day names from Date column

    Date Select = if(Sample[Date] = "Wednesday",Sample[Date],Blank())  - I need the sum(PrincipalBalance) every wednesday for entire week starting Thursday to Wednesday. i.e 10/25/2017 - Wednesday sum(balance) from 10/19/2017 - 10/25/2017.

     

    I have created measures:

     

    Current = CALCULATE(COUNT(Sample[lD]),FILTER(Sample,Sample[Number]< 1))

    1-2 Weeks = CALCULATE(COUNT(Sample[lD]),FILTER(Sample,Sample[Number] < 3))

    3-4 Weeks = CALCULATE(COUNT(Sample[lD]),FILTER(Sample,Sample[Number] < 5))

    5+ Weeks = CALCULATE(COUNT(Sample[lD]),FILTER(Sample,Sample[Number] >= 5))

    Sum = Current + 1-2 Weeks + 3-4 Weeks +( 5+ Weeks)

     

    Data Display

     

    Matrix

    Rows: Date Select

    Column: Months(Date)

    Values: Sum

     

     

     

     

    Now I need the difference between 25th Oct and 18th Oct, 18th Oct and 11th Oct and so on...

     

     

    Number        Balance       Date

    0.00              1000            10/24/2017
    0.00               2000            10/16/2017
    1.00     7000                      10/10/2017
    0.00      1000                      10/27/2017
    0.00       2000                      10/17/2017
    1.10        5000                      10/24/2017
    -6.57        3500                        10/10/2017
    17.61        2500                         10/2/2017

     

    I need to acheive this. 

    Please Help.

     

    Thanks alot for your time

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi ApurvaKhatri,

       

      No one here is intested in your actual data.  Dummy your dataset and then share the result of that dummy dataset.  The dataset that you paste here should be such that can be easily copied into an Excel file.

      • ApurvaKhatri's avatar
        ApurvaKhatri
        Helper III
        8 years ago

        Hello Ashish_Mathur

         

        NumberBalance Date
        06162510/20/2017 6:15
        067219.9210/15/2017 6:15
        2.2659320.4110/10/2017 6:15
        0.2282904.5510/25/2017 6:15
        089217.9210/18/2017 6:15
        05880010/19/2017 6:15
        089868.4810/20/2017 6:15
        -17400010/15/2017 6:15
        06840010/20/2017 6:15
        07245010/2/2017 6:15
        47030010/11/2017 6:15
        06840010/12/2017 6:15
        091383.1610/18/2017 6:15
        489157510/14/2017 6:15
        17077510/22/2017 6:15
        -0.0188788.7410/14/2017 6:15
        -0.7960823.1910/8/2017 6:15
        07122510/13/2017 6:15
        07215010/11/2017 6:15
        07125010/21/2017 6:15
        069837.510/10/2017 6:15
        47.1597570.810/12/2017 6:15
        06240010/25/2017 6:15
        16045010/26/2017 6:15

         

        I am unable to attach the excel file.