Forum Discussion

TechR21's avatar
TechR21
Helper V
3 years ago
Solved

Adding row with a calculation to matrix visual

Hi,

 

In powerbi I have a simple matrix visual :

- subscriptionname as row

- month as column

- sum of costs as value

 

 

Im trying to find a way to add the following to this matrix (or any other way possible to show this)

- a trend value that shows the difference in percentage between the totals of two months, so the difference between July & June, and June & May totals.

 

June: 2.11% (154228.14 / 151042.66 * 100)

July: 8.78 % (167772.21 / 154228.14 * 100)

 

The thing is that at the beginning of each month, a new set of data of the last month get into the table. So August data will be automatically added in the first week in September.

 

Is there a way to have some sort of dynamic calculation here that i can use so that when a new month is added, the calculation will be done for the previous month?

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi TechR21 ,

     

    I suggest you to create a Calendar table to help your calculation.

    Calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "Month-Year", FORMAT ( [Date], "MMMM-YY" )
)

    Data model:

    Measure:

    Percentage Change by Month =
VAR _CURRENT =
    CALCULATE ( SUM ( 'Table'[Costs] ) )
VAR _PREVIOUS =
    CALCULATE ( SUM ( 'Table'[Costs] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
RETURN
    IF ( _CURRENT = BLANK (), BLANK (), DIVIDE ( _CURRENT - _PREVIOUS, _PREVIOUS ) )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    3 years ago

    Hey TechR21 ,

     

    I recommend reading this article: https://www.daxpatterns.com/time-patterns/

    The article contains almost everything necessary to know regarding calendar based calculations.

     

    It explains how you create a dedicated calendar table, relate this to your fact table(s), and how to create dynamic date based calculations.

     

    Regards,

    Tom

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi TechR21 ,

     

    I suggest you to create a Calendar table to help your calculation.

    Calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "Month-Year", FORMAT ( [Date], "MMMM-YY" )
)

    Data model:

    Measure:

    Percentage Change by Month =
VAR _CURRENT =
    CALCULATE ( SUM ( 'Table'[Costs] ) )
VAR _PREVIOUS =
    CALCULATE ( SUM ( 'Table'[Costs] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
RETURN
    IF ( _CURRENT = BLANK (), BLANK (), DIVIDE ( _CURRENT - _PREVIOUS, _PREVIOUS ) )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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