Forum Discussion

JPScotland's avatar
JPScotland
Icon for Helper I rankHelper I
6 years ago
Solved

Week on week differences based on specfic field account number

Hi All,

 

I have been reading a few posts that are similar but don't quite seem to be the same problem so hoping you can help 🙂

 

My sample data has multiple rows that show the Account Number with the weekly end period date and the period balance at that point.  Each week a new record is created for each account.

 

I would like to show the difference week on week but unique to the account number.  i.e. Account Number 100 shows 14 weekly balances so far in this financial year.  So I need to see the difference from 05-Apr-20 to 12-Apr-20 WHERE AccountID=100 and so on.  

 

Any help would be much appreciated. 

 

Account IDPeriod End DatePeriod Balance
10005-Apr-20-£14.83
10012-Apr-20-£37.41
10019-Apr-20-£25.81
10026-Apr-20-£25.81
10003-May-20-£14.14
10010-May-20-£49.15
10017-May-20-£37.48
10024-May-20-£25.81
10031-May-20-£14.14
10007-Jun-20-£49.15
10014-Jun-20-£37.47
10021-Jun-20-£25.81
10028-Jun-20-£14.14
10005-Jul-20-£49.15
22005-Apr-20-£344.20
22012-Apr-20-£262.05
22019-Apr-20-£179.90
22026-Apr-20-£97.76
22003-May-20-£15.60
22010-May-20-£289.43
22017-May-20-£207.50
22024-May-20-£125.13
22031-May-20-£42.98
22007-Jun-20-£316.81
22014-Jun-20-£234.66
22021-Jun-20-£152.51
22028-Jun-20-£70.36
22005-Jul-20-£344.19
33005-Apr-20-£0.34
33012-Apr-20£74.72
33019-Apr-20-£175.48
33026-Apr-20-£100.49
33003-May-20-£25.36
33010-May-20£49.70
33017-May-20£124.70
33024-May-20-£125.41
33031-May-20-£50.38
33007-Jun-20£24.68
33014-Jun-20£99.74
33021-Jun-20-£150.47
33028-Jun-20-£75.40
33005-Jul-20-£0.34
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    JPScotland it should be easy, check this video.

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi JPScotland ,

     

    If you want a measure, you can refer Parry and amitchandak’s answer.

     

    If you want a calculate column, we can use the following formula to meet your requirement.

     

    difference Column =
VAR _lastweek =
    CALCULATE (
        SUM ( 'Table'[Period Balance] ),
        FILTER (
            'Table',
            'Table'[Period End Date]
                = EARLIER ( 'Table'[Period End Date] ) - 7
                && 'Table'[Account ID] = EARLIER ( 'Table'[Account ID] )
        )
    )
RETURN
    IF ( ISBLANK ( _lastweek ), BLANK (), 'Table'[Period Balance] - _lastweek )

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    BTW, pbix as attached.

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    JPScotland it should be easy, check this video.

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi JPScotland ,

     

    If you want a measure, you can refer Parry and amitchandak’s answer.

     

    If you want a calculate column, we can use the following formula to meet your requirement.

     

    difference Column =
VAR _lastweek =
    CALCULATE (
        SUM ( 'Table'[Period Balance] ),
        FILTER (
            'Table',
            'Table'[Period End Date]
                = EARLIER ( 'Table'[Period End Date] ) - 7
                && 'Table'[Account ID] = EARLIER ( 'Table'[Account ID] )
        )
    )
RETURN
    IF ( ISBLANK ( _lastweek ), BLANK (), 'Table'[Period Balance] - _lastweek )

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    BTW, pbix as attached.

  • JPScotland's avatar
    JPScotland
    Icon for Helper I rankHelper I
    6 years ago

    Thank you all for replying.  I actually looked at everyones comments and I think you all answered it.  It also taught me a few new things which is great.   

     

    I went with v-zhenbw-msft answer as I am still new to PowerBI and the code was just ready to lift straight into the report.  

     

    Thanks again all.

     

    JP