Forum Discussion

Cheryl314's avatar
Cheryl314
New Member
2 years ago
Solved

Calculate difference every month in cumulative values

Hi All,

I'm a beginner - and I'm not a programmer.

I get a data dump each month of cumulative 'views' documents have received in a Sharepoint library.

This view count is from the time the document was uploaded on the system to date the report is extracted (lifetime views).

I need to be able to find that difference month-on-month and show those values.

 

SourceDocumentLifetime Views
Library 1 Mar 2024Document 110
Library 1 Mar 2024Document 22
Library 1 Mar 2024Document 38
Library 1 Mar 2024Document 417
Library 1 Mar 2024Document 516
Library 1 Mar 2024Document 68
Library 1 Apr 2024Document 422
Library 1 Apr 2024Document 517
Library 1 Apr 2024Document 616
Library 1 Apr 2024Document 756
Library 2 Feb 2024Document 849
Library 2 Feb 2024Document 921
Library 2 Mar 2024Document 1012
Library 3 Feb 2024Document 1114
Library 3 Feb 2024Document 123
Library 3 Feb 2024Document 131
Library 3 Feb 2024Document 149
Library 3 Mar 2024Document 1114
Library 3 Mar 2024Document 123
Library 3 Mar 2024Document 137
Library 3 Mar 2024Document 149
Library 3 Mar 2024Document 1515

 

So, eg: Document Document 4 had 17 lifetime views in Mar '24 & 22 in Apr '24, effectively having 5 new views in Apr '24.

Similarly, Document 14 had 9 views in both Mar & Apr - effectively showing 0 views in Apr.
That's the number I want to derive.

Appreciate any ideas!

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    Cheryl314 , You can use DAX for this

    First create a new calculated column to extract month and year

    MonthYear = FORMAT([Date], "MMM YYYY")

     

    Second create a measure 

     

    Difference =
    VAR CurrentDocument = SELECTEDVALUE('Table'[SourceDocument])
    VAR CurrentMonthYear = SELECTEDVALUE('Table'[MonthYear])
    VAR PreviousMonthYear = CALCULATE(MAX('Table'[MonthYear]), 'Table'[SourceDocument] = CurrentDocument && 'Table'[MonthYear] < CurrentMonthYear)
    VAR CurrentViews = CALCULATE(SUM('Table'[LifetimeViews]), 'Table'[SourceDocument] = CurrentDocument && 'Table'[MonthYear] = CurrentMonthYear)
    VAR PreviousViews = CALCULATE(SUM('Table'[LifetimeViews]), 'Table'[SourceDocument] = CurrentDocument && 'Table'[MonthYear] = PreviousMonthYear)
    RETURN IF(ISBLANK(PreviousMonthYear), 0, CurrentViews - PreviousViews)

     

    Now create a matrix or table visualization using Measure

     

1 Reply

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    Cheryl314 , You can use DAX for this

    First create a new calculated column to extract month and year

    MonthYear = FORMAT([Date], "MMM YYYY")

     

    Second create a measure 

     

    Difference =
    VAR CurrentDocument = SELECTEDVALUE('Table'[SourceDocument])
    VAR CurrentMonthYear = SELECTEDVALUE('Table'[MonthYear])
    VAR PreviousMonthYear = CALCULATE(MAX('Table'[MonthYear]), 'Table'[SourceDocument] = CurrentDocument && 'Table'[MonthYear] < CurrentMonthYear)
    VAR CurrentViews = CALCULATE(SUM('Table'[LifetimeViews]), 'Table'[SourceDocument] = CurrentDocument && 'Table'[MonthYear] = CurrentMonthYear)
    VAR PreviousViews = CALCULATE(SUM('Table'[LifetimeViews]), 'Table'[SourceDocument] = CurrentDocument && 'Table'[MonthYear] = PreviousMonthYear)
    RETURN IF(ISBLANK(PreviousMonthYear), 0, CurrentViews - PreviousViews)

     

    Now create a matrix or table visualization using Measure