Forum Discussion

jbenson's avatar
jbenson
Helper III
5 years ago
Solved

Difference Between Months

I have a table that has a corn measure total column and a bean measure total column which then adds the two together and goes into a grain total column. I have a slicer setup to view the information by month. If I two months selected to compare the two, the total adds the two months together instead of subtracting to find the difference. I have tried the quick measure tool with no success  and I have looked over many forums with no luck. i might not be understanding the formula's that i have found. 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi jbenson ,

    Looks like you need to create a month number column first in order to compare, text month column could not be used to compare if you do not have a date column.

    MonthNo. = 
SWITCH (
    'Table'[Month],
    "JAN", 1,
    "FEB", 2,
    "MAR", 3,
    "APR", 4,
    "MAY", 5,
    "JUN", 6,
    "JUL", 7,
    "AUG", 8,
    "SEPT", 9,
    "OCT", 10,
    "NOV", 11,
    "DEC", 12
)

    Use the monthNo. column as a slicer and create a measure like this to calculate the difference:

    Difference =
VAR maxmonth =
    CALCULATE (
        SUM ( 'Table'[Total Grain Measure] ),
        FILTER (
            'Table',
            'Table'[MonthNo.]
                = CALCULATE ( MAX ( 'Table'[MonthNo.] ), ALLSELECTED ( 'Table' ) )
        )
    )
VAR minmonth =
    CALCULATE (
        SUM ( 'Table'[Total Grain Measure] ),
        FILTER (
            'Table',
            'Table'[MonthNo.]
                = CALCULATE ( MIN ( 'Table'[MonthNo.] ), ALLSELECTED ( 'Table' ) )
        )
    )
RETURN
    maxmonth - minmonth

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi jbenson 

    Show a sample of your data and the expected result. Include the code of your current relevant measures

     

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

    • jbenson's avatar
      jbenson
      Helper III
      5 years ago

      My corn measure and my bean measure are added to the total grain by month. most of the months are the same at this time because it is a new model so i just copied the same data over for each month. But when i have both December and January selected it will add them together instead of subtracting the difference. I haven't found a good code to use so i don't have any

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello jbenson 

     

    you have to create a date-table, create a relationship with your fact-table and create measure that makes the sum of the month in your current context and subract the sum of the month of your previous month ot your current context. I'm not a DAX expert, but this could lead you in the right direction.

    CurrentMinusprevMonth = Sum('Table'[Value])-CALCULATE(  
   SUM('Table'[Value]),  
   PREVIOUSMONTH('date'[Date])
)

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi jbenson ,

    Looks like you need to create a month number column first in order to compare, text month column could not be used to compare if you do not have a date column.

    MonthNo. = 
SWITCH (
    'Table'[Month],
    "JAN", 1,
    "FEB", 2,
    "MAR", 3,
    "APR", 4,
    "MAY", 5,
    "JUN", 6,
    "JUL", 7,
    "AUG", 8,
    "SEPT", 9,
    "OCT", 10,
    "NOV", 11,
    "DEC", 12
)

    Use the monthNo. column as a slicer and create a measure like this to calculate the difference:

    Difference =
VAR maxmonth =
    CALCULATE (
        SUM ( 'Table'[Total Grain Measure] ),
        FILTER (
            'Table',
            'Table'[MonthNo.]
                = CALCULATE ( MAX ( 'Table'[MonthNo.] ), ALLSELECTED ( 'Table' ) )
        )
    )
VAR minmonth =
    CALCULATE (
        SUM ( 'Table'[Total Grain Measure] ),
        FILTER (
            'Table',
            'Table'[MonthNo.]
                = CALCULATE ( MIN ( 'Table'[MonthNo.] ), ALLSELECTED ( 'Table' ) )
        )
    )
RETURN
    maxmonth - minmonth

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.