Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Summarizing the differences between months

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?
 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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)

3 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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)