Forum Discussion

chris_m's avatar
chris_m
Helper 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 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?

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

5 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community 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
      Helper 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
        Community 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] )
)