Summarizing the differences between months

[Anonymous]

Hello,

I am trying to summarize the differences between each month's values compared to the previous month in a matrix.

Right now, I have this measure, which results in the correct sums and totals in every case, EXCEPT when there is no value for a month. Then, the calculation SHOULD show the negative value from the previous month (difference = this month - previous month) but instead it shows nothing, and also the total for the row also goes wrong. 

The measure 

Diff =

IF(HASONEFILTER('Data'[Year/Month]),

CALCULATE(SUM('Data'[EUR])- CALCULATE(SUM('Data'[EUR]), PREVIOUSMONTH('Data'[Year/Month]))),

CALCULATE(SUM('Data'[EUR]), FILTER('Data', 'Data'[Year/Month] = MAX('Data'[Year/Month]))) -CALCULATE(SUM('Data'[EUR]), PREVIOUSMONTH('Data'[Year/Month])))

Attached are pictures from the matrix in Power BI, and a table from excel which shows how the values should be. 

How the line looks in the MatrixHow the line should look like (Calculated manually in excel)

How should the measure be modified in order to achieve this?

[Anonymous · Accepted solution]

Hello

The issue was fixed with the following syntax:

Diff =
VAR Diff =
CALCULATE(SUMX('Data','Data'[EUR]) -CALCULATE(SUMX('Data','Data'[EUR]), PREVIOUSMONTH('Date Table'[Date])))
VAR Total =
CALCULATE(SUMX('Data','Data'[EUR]), FILTER('Data', 'Data'[Year/Month] = MAXX(ALL('Data'),'Data'[Year/Month]))) -CALCULATE(SUMX('Data','Data'[EUR]), PREVIOUSMONTH('Date Table'[Date]))

RETURN
IF(HASONEFILTER('Date Table'[Year-month]),Diff,Total)

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[v-yanjiang-msft]

Hi @Anonymous ，

According to your description, I need more information.

Is the value showed in your snapshot 'Data'[EUR] or measure Diff, if it's measure Diff, please show the corresponding 'Data'[EUR].

In your first snapshot, you covered the value of 2022-3, 2022-4 and 2022-5, are they blank or zero?

Waiting for your reply.

Best Regards,
Community Support Team _ kalyj