Forum Discussion

chris_m's avatar
chris_m
Icon for Helper I rankHelper I
9 years ago
Solved

Weekly Variance between snapshots / time periods

I'm trying to replace an old excel report with a better version using Power BI, but I am having trouble creating a measure that will replicate the results of the old report.    I have two time dime...
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

    chris_m,

     

    You may refer to the following DAX that creates a new table.

    Table =
ADDCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZE (
            Sheet1,
            Sheet1[CPS WEEK],
            Sheet1[Pack Week],
            "Sum of Containers", SUM ( Sheet1[No. of containers] )
        ),
        "Prev", SUMX (
            FILTER (
                Sheet1,
                Sheet1[CPS WEEK]
                    = EARLIER ( Sheet1[CPS WEEK] ) - 1
                    && Sheet1[Pack Week] = EARLIER ( Sheet1[Pack Week] )
            ),
            Sheet1[No. of containers]
        )
    ),
    "Variance", IF ( ISBLANK ( [Prev] ), BLANK (), [Sum of Containers] - [Prev] )
)
v-chuncz-msft's avatar
v-chuncz-msft
Icon for Community Support rankCommunity Support
9 years ago

chris_m,

 

Your requirement is not very clear to us. Better to provide some sample data to better illustrate this problem.

chris_m's avatar
chris_m
Icon for Helper I rankHelper I
9 years ago

v-chuncz-msft

 

Hi,

 

Yeah sorry, it wasn't very good!

What I want to measure is the variance between the snapshots, e.g.  Sum of Containers for Snapshot 16 Pack Week 17 vs. Sum of Containers for Snapshot 15 Pack Week 17

 

I'll send some sample data to you over PM.

 

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    chris_m,

     

    You may refer to the following DAX that creates a new table.

    Table =
ADDCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZE (
            Sheet1,
            Sheet1[CPS WEEK],
            Sheet1[Pack Week],
            "Sum of Containers", SUM ( Sheet1[No. of containers] )
        ),
        "Prev", SUMX (
            FILTER (
                Sheet1,
                Sheet1[CPS WEEK]
                    = EARLIER ( Sheet1[CPS WEEK] ) - 1
                    && Sheet1[Pack Week] = EARLIER ( Sheet1[Pack Week] )
            ),
            Sheet1[No. of containers]
        )
    ),
    "Variance", IF ( ISBLANK ( [Prev] ), BLANK (), [Sum of Containers] - [Prev] )
)
    • chris_m's avatar
      chris_m
      Icon for Helper I rankHelper I
      9 years ago

      Wow, thats a very impressive formula! Thank you for your time and effort.  I don't think I ever would have got there. 

       

      I constrained the data that I sent to you for confidentiality reasons, but I have been able to modify your formula slightly in order to incorporate it into my model without any hassle. 

      If I wanted to measure the variance from two weeks prior, could I add another name and SUMX function to the formula for using essentially the same code? 

       

      E.g. 

              "Prev 2", SUMX (
            FILTER (
                Sheet1,
                Sheet1[CPS WEEK]
                    = EARLIER ( Sheet1[CPS WEEK] ) - 2
                    && Sheet1[Pack Week] = EARLIER ( Sheet1[Pack Week] )
            ),
            Sheet1[No. of containers]