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 ...
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.

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