Weekly Variance between snapshots / time periods

[chris_m]

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 dimensions to my data - the CPS or Snapshot Date (i.e. when we received the data) and the Pack or Execution Date (i.e. when the event will occur). I report these in weekly buckets. 

I have two date tables - Date CPS and Date Pack. 

As an example, this is what it currently looks like:

Reference week Syntax = 'CPS' & 'CPS Week' & 'Pack Week' 

The cells in the table do a SUMIF using those reference weeks, e.g, SUMIF values for CPS Week 16 Pack Week 17 minus values for CPS Week 15 Pack Week 17. 

I've tried a number of formulas but none seem to get the result i'm trying to replicate, for example:

SUM TEU CPS -1 =
CALCULATE (
    [SUM TEU],
    FILTER ( 'Date CPS', 'Date CPS'[DateKey] = ( MAX ( 'Date CPS'[DateKey] ) - 7 ) )
)

Returns blank. 

I'm not restricted to presenting the data in a matrix or table. Any suggestions?

[v-chuncz-msft]

@chris_m,

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

[chris_m]

@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 · Accepted solution]

@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]

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]

[v-chuncz-msft]

@chris_m,

Yes, you can do that.