Forum Discussion

JBI's avatar
JBI
Frequent Visitor
7 years ago

Difference between two points

Hi Guys,

 

I have data that is not transactional. Each entry is the current value for that month. Thus it's not data such as sales that can be summed but rather a single value at a point in time.

My objective is to determine the difference between the value for this month and six months prior. All the examples use a variation of this Datesbetween formula:

 

Delta =
CALCULATE(sum(Sales[Monthly Sales]),
DATESBETWEEN('Calendar'[Date],
Max('Calendar'[Date]) - 180,
Max('Calendar'[Date])))
 
This doesn't work as I don't 'Sum' my data. See the image below:
 

 

I just want to do simple math and determine 2000 (From this month) - 2500 (From 6 months ago) = -500. Can't get it right at all!
 
Thanks
 
 
 
 
 
Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    You could try something like 

    CALCULATE(

                        SUM(

                                 Sales[Monthly Sales]

                                )

                        )

    -

    CALCULATE(

                        SUM(

                                 Sales[Monthly Sales]),

                        FILTER(

                                   ALL('Calendar'), 

                                   'Calendar'[Date]=Max('Calendar'[Date]) - 180

                                   )

                       )

     

    BR

    • JBI's avatar
      JBI
      Frequent Visitor
      7 years ago

      It's still returning the whole table. I want a scalar value returned. Surely there should be a simple way to do calculations like this in PowerBI

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi JBI ,

        Have you solved your problem?

        If you have solved, please  always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

        If you still need help, please feel free to ask.

        Best Regards,

        Cherry

         

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi JBI ,

    For your requirement, you could refer to the measure below. You could modify it based on your scenario.

    Measure =
VAR c_m =
    CALCULATE (
        SUM ( 'Sales'[Project Sales] ),
        FILTER (
            'Calendar',
            'Calendar'[Month] = MAX ( 'Sales'[Month] )
                && 'Calendar'[Date].[Year] = MAX ( 'Sales'[year] )
        )
    )
VAR c_m_6 =
    CALCULATE (
        SUM ( 'Sales'[Project Sales] ),
        DATEADD ( 'Sales'[Date_column], -6, MONTH )
    )
RETURN
    c_m - c_m_6

    Here is the output.

    For more details, please refer to my attchment.

    Best  Regards,

    Cherry