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,

 

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]