Forum Discussion

jotapece's avatar
jotapece
Helper I
8 years ago
Solved

Total previous value

Helo.   I have this data:   And I have this model:   I have this meassure: I used SUM and not SUMX beacause really I have two tables.   I have this other meassure:   This i...
  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    here you will find a PBIX file. This file contains a really small amount of sample data :-)

    But besides that it's really close to your model.

     

    I create these two measures:

    A plus B = SUM(Table1[A]) + SUM(Table1[B])

    and

    PrevMonth A plus B = 
CALCULATE(
    [A plus B]
    ,PREVIOUSMONTH('Calendar'[Date])
)

    Please be aware that I|m using the Calendar table in the measure to calculate the Previous Month Value.

     

    As you can see from this screenshot both measure are recreating the primary issue, no sum for the total row

     

    There is no value, because there is no active filter from the Calendar table.

     

    For this reason, it's necessary to use SUMX() to iterate across the months.

     

    The DAX statement for the measure I provided in my previous post was creating weird values due to the fact, the for each Day of the current month the previous month value was added.

     

    For this it's necessary to iterate across the months and not the days.

    Be aware that I'm using the column "Year-Month" from my Calendar table.

    here is the measure:

    SUMX PrevMonth A plus B = 
SUMX(
    VALUES('Calendar'[Year-Month])
    ,[PrevMonth A plus B]
)

    And now it looks like this

     

     I would recommend that you change the relationship between your tables from 1:1 to 1 (your calendar table) to many (your fact table) and also adjust the filter direction from Both to Single, in most of the cases this is sufficient :-)

     

    Hopefully this is what you are lookinf for

     

    Regards

    Tom

TomMartens's avatar
TomMartens
Super User
8 years ago

Hey,

 

give this a try:

Previous A plus B =
SUMX(
'Table1'[Fecha]
,[Previous A plus B]
)

Hopefully this is what you are looking for

 

Regards

Tom

 

 

jotapece's avatar
jotapece
Helper I
8 years ago
TomMartens wrote:

give this a try:

Previous A plus B =
SUMX(
'Table1'[Fecha]
,[Previous A plus B]
)

 

Hi TomMartens

 

I think there's something wrong in your code. SUMX first parameter must be a table, isn't it?

 

Thanks!

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago
    Hey,

    i forgot to encapsulate the column reference into a VALUES(), this turns the filtered values of the column into a 1-column table.

    Regards
    Tom
    • jotapece's avatar
      jotapece
      Helper I
      8 years ago

      TomMartens, I having issues.... 

       

      Captura

      Now I get the total, but I don't understand what's the measure is doing.

       

      Any Ideas?

       

      Thanks!

      • TomMartens's avatar
        TomMartens
        Super User
        8 years ago

        Hey,

         

        here you will find a PBIX file. This file contains a really small amount of sample data :-)

        But besides that it's really close to your model.

         

        I create these two measures:

        A plus B = SUM(Table1[A]) + SUM(Table1[B])

        and

        PrevMonth A plus B = 
CALCULATE(
    [A plus B]
    ,PREVIOUSMONTH('Calendar'[Date])
)

        Please be aware that I|m using the Calendar table in the measure to calculate the Previous Month Value.

         

        As you can see from this screenshot both measure are recreating the primary issue, no sum for the total row

         

        There is no value, because there is no active filter from the Calendar table.

         

        For this reason, it's necessary to use SUMX() to iterate across the months.

         

        The DAX statement for the measure I provided in my previous post was creating weird values due to the fact, the for each Day of the current month the previous month value was added.

         

        For this it's necessary to iterate across the months and not the days.

        Be aware that I'm using the column "Year-Month" from my Calendar table.

        here is the measure:

        SUMX PrevMonth A plus B = 
SUMX(
    VALUES('Calendar'[Year-Month])
    ,[PrevMonth A plus B]
)

        And now it looks like this

         

         I would recommend that you change the relationship between your tables from 1:1 to 1 (your calendar table) to many (your fact table) and also adjust the filter direction from Both to Single, in most of the cases this is sufficient :-)

         

        Hopefully this is what you are lookinf for

         

        Regards

        Tom