cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chris_m Regular Visitor
Regular Visitor

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 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 15Capture1.PNG 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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Weekly Variance between snapshots / time periods

@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] )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Weekly Variance between snapshots / time periods

@chris_m,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
chris_m Regular Visitor
Regular Visitor

Re: Weekly Variance between snapshots / time periods

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

 

Community Support Team
Community Support Team

Re: Weekly Variance between snapshots / time periods

@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] )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

chris_m Regular Visitor
Regular Visitor

Re: Weekly Variance between snapshots / time periods

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]

 

 

 

 

Community Support Team
Community Support Team

Re: Weekly Variance between snapshots / time periods

@chris_m,

 

Yes, you can do that.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,937)