Calculate difference every month in cumulative values

[Cheryl314]

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.

Source	Document	Lifetime Views
Library 1 Mar 2024	Document 1	10
Library 1 Mar 2024	Document 2	2
Library 1 Mar 2024	Document 3	8
Library 1 Mar 2024	Document 4	17
Library 1 Mar 2024	Document 5	16
Library 1 Mar 2024	Document 6	8
Library 1 Apr 2024	Document 4	22
Library 1 Apr 2024	Document 5	17
Library 1 Apr 2024	Document 6	16
Library 1 Apr 2024	Document 7	56
Library 2 Feb 2024	Document 8	49
Library 2 Feb 2024	Document 9	21
Library 2 Mar 2024	Document 10	12
Library 3 Feb 2024	Document 11	14
Library 3 Feb 2024	Document 12	3
Library 3 Feb 2024	Document 13	1
Library 3 Feb 2024	Document 14	9
Library 3 Mar 2024	Document 11	14
Library 3 Mar 2024	Document 12	3
Library 3 Mar 2024	Document 13	7
Library 3 Mar 2024	Document 14	9
Library 3 Mar 2024	Document 15	15

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!

[bhanu_gautam · Accepted solution]

@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