Difference between two points

[JBI]

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

[v-piga-msft]

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

[Anonymous]

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]

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]

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

[JBI]

Hi,

Did not get solution no. I got a solution on a different feed.

Thanks

[v-piga-msft]

Hi @JBI ,

If you get a solution, could you share and accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry