Forum Discussion

di12's avatar
di12
Frequent Visitor
4 years ago
Solved

Week over week comparison for matrix

This should be very easy, but I did not manage to get solution.

 

I have 3 columns of data: Name , Amount, Date

 

I would like to have a pivot at the end with week over week comparison, i.e. like in below matrix

------- week 21 | WoW | week 22 | WoW | week23

Jack       100         20%        120        -50%    60

John      10           20%         12        -50%    6

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

    I tried to create a sample pbix file.

    I suggest having a dim-calendar table that contains week information, like the attached.

    I hope this sample can help to get your own solution for your own data model.

     

     

    Amount total: = 
SUM( Data[Amount] )

     

    current wk vs prior wk: = 
VAR _currentwkamount = [Amount total:]
VAR _currentwkenddate =
    MAX ( 'Calendar'[End of Week] )
VAR _priorwkenddate = _currentwkenddate - 7
VAR _priorwkamount =
    CALCULATE (
        [Amount total:],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[End of Week] = _priorwkenddate )
    )
RETURN
    IF (
        HASONEVALUE ( 'Calendar'[WK-Year] ),
        DIVIDE ( _currentwkamount - _priorwkamount, _priorwkamount )
    )

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

    I tried to create a sample pbix file.

    I suggest having a dim-calendar table that contains week information, like the attached.

    I hope this sample can help to get your own solution for your own data model.

     

     

    Amount total: = 
SUM( Data[Amount] )

     

    current wk vs prior wk: = 
VAR _currentwkamount = [Amount total:]
VAR _currentwkenddate =
    MAX ( 'Calendar'[End of Week] )
VAR _priorwkenddate = _currentwkenddate - 7
VAR _priorwkamount =
    CALCULATE (
        [Amount total:],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[End of Week] = _priorwkenddate )
    )
RETURN
    IF (
        HASONEVALUE ( 'Calendar'[WK-Year] ),
        DIVIDE ( _currentwkamount - _priorwkamount, _priorwkamount )
    )