Calculates the sum of the values of the last available period minus the previous period

[Charline_74]

Hi, 

I'm trying to calculate: the sum of the values for the last period available in my table minus the previous period.

Example
Last available period: 12-23
Previous period: 11-23

Of course the periods will change over time

Thank you

[Charline_74]

I can't use Edit Interactions because in my table I also have measures with selected values, so I tested ALL()

[frithjof_v]

Is the measure not freezing after using ALL() in the DAX code? Hm... I'm surprised by that. 

[frithjof_v]

Does it help to edit this part of the measure, instead of using <> use just < instead:

// Find the second latest period which has row(s) in your table:

VAR _previousPeriod = CALCULATE(MAX(TableName[Period]), ALL(TableName[Period]), TableName[Period] < _lastAvailablePeriod)

[Charline_74]

No it doesn't work ...

[frithjof_v · Accepted solution]

Hm... I made a test, this works on my side. No change in the measure's value, even if I change the selection in the period slicer:

SumLastAvailablePeriodMinusPreviousPeriodAll =

// Find the latest period for which there exist row(s) in your table:

VAR _lastAvailablePeriod = CALCULATE(MAX(TableName[Period]), ALL())

// Calculate the sum of values for the latest period:

VAR _sumValuesLastAvailablePeriod = CALCULATE(SUM(TableName[Values]), TableName[Period] = _lastAvailablePeriod)

// Find the second latest period which has row(s) in your table:

VAR _previousPeriod = CALCULATE(MAX(TableName[Period]), ALL(), TableName[Period] < _lastAvailablePeriod)

// Calculate the sum of values for the second latest period:

VAR _sumValuesPreviousPeriod = CALCULATE(SUM(TableName[Values]), TableName[Period] = _previousPeriod)

RETURN

// Your output will be the sum of values for the latest period minus the sum of values for the second latest period:

_sumValuesLastAvailablePeriod - _sumValuesPreviousPeriod


I entered som test data like this:
 

And I got the expected results: (1200 + 1200) - (1100 + 1100) = 200. The measure is ignoring the Period slicer because of the ALL() function in the measure. I did not use Edit interactions.

[govindarajan_d]

Hi @Charline_74,

If you don't have a date dimension, please create one and connect the date dimension to the fact table. 

If you have a date table that is already filtered till last month:

For calculating the current:

CurrentMonthSum = CALCULATE(SUM(Sales[SalesValue]), DATESMTD(Date[Date]))

For calculating the previous:

PreviousMonthSum = CALCULATE(SUM(Sales[SalesValue]),PREVIOUSMONTH(Date[Date]))

Difference  = [CurrentMonthSum] - [PreviousMonthSum].

If you want absolute difference, please use ABS() in the above difference formula. 

[frithjof_v]

Do you want to do the calculation in DAX language, or another language?

Will you use the calculation in a measure in Power BI?

[Charline_74]

Hi, 
Yes in DAX

[frithjof_v]

One approach would be to use this kind of logic:

SumLastAvailablePeriodMinusPreviousPeriod = 

// Find the latest period for which there exist row(s) in your table:
VAR _lastAvailablePeriod = MAX(TableName[Period])

// Calculate the sum of values for the latest period:

VAR _sumValuesLastAvailablePeriod = CALCULATE(SUM(TableName[Values]), TableName[Period] = _lastAvailablePeriod))

// Find the second latest period which has row(s) in your table:
VAR _previousPeriod = CALCULATE(MAX(TableName[Period]), TableName[Period] < _lastAvailablePeriod)

// Calculate the sum of values for the second latest period:

VAR _sumValuesPreviousPeriod = CALCULATE(SUM(TableName[Values]), TableName[Period] = _previousPeriod))

RETURN

// Your output will be the sum of values for the latest period minus the sum of values for the second latest period:
_sumValuesLastAvailablePeriod - _sumValuesPreviousPeriod

This requires that you have a [Period] column on a format like YYYYMM (or another format which enables sorting) because that would allow you to find the latest period which has values in your fact table. The current format of your periods ("MM-YY") is not suitable for finding the latest period, because with the current format of the [Period] column the MAX function will sort by month first and then year but you need a format which sorts by year first and then month like YYYYMM.

So it would need to use a column which is on the YYYYMM format (or another format which allows for sorting of periods). I would change the format of the [Period] column to a sortable format like YYYYMM, or use another period column which has a sortable format.

The strength of using this method, is that this method looks in your fact table to find what is the latest available period in your fact table (the latest period which contains values in your fact table). It does that by using the MAX function. It then sums the values for this period.

Then it finds the previous* period which has values in your fact table, and sums the values of that period.

Then it takes the sum of last period minus sum of previous period.

* It actually finds the second latest period which has rows(s) in your table.

If the last period which has rows in your fact table is "12-23" and also period "11-23" has one or more rows in the fact table, then this DAX code will take the sum of values for period "12-23" minus the sum of values for period "11-23".

However, if the last period which has rows in your fact table is "12-23" and there are no rows for period "11-23" but there are rows for "10-23", then this DAX code will take the sum of values for "12-23" minus the sum of values for "10-23".

The solution could be modified to handle such cases, if that is a relevant issue.

[Charline_74]

Hi, 
Thanks for your feedback, the formula works but I have a problem. I have a slicer with my periods and when I click on a period the measure changes.
Do you have a solution for freezing the measure?

[frithjof_v]

I can think of two alternatives:

Alt. 1: You can use the Edit interactions functionality on the slicer to ensure that the slicer selection will not affect the visual which is using the measure.

Alt. 2: Include the ALL function in the measure. This should make the measure unaffected by any selections made by the user. Then the measure would be something like this:

SumLastAvailablePeriodMinusPreviousPeriod = 

// Find the latest period for which there exist row(s) in your table:

VAR _lastAvailablePeriod = CALCULATE(MAX(TableName[Period]), ALL(TableName[Period]))

// Calculate the sum of values for the latest period:

VAR _sumValuesLastAvailablePeriod = CALCULATE(SUM(TableName[Values]), TableName[Period] = _lastAvailablePeriod)

// Find the second latest period which has row(s) in your table:

VAR _previousPeriod = CALCULATE(MAX(TableName[Period]), ALL(TableName[Period]), TableName[Period] < _lastAvailablePeriod)

// Calculate the sum of values for the second latest period:

VAR _sumValuesPreviousPeriod = CALCULATE(SUM(TableName[Values]), TableName[Period] = _previousPeriod)

RETURN

// Your output will be the sum of values for the latest period minus the sum of values for the second latest period:

_sumValuesLastAvailablePeriod - _sumValuesPreviousPeriod

I think this should work if the slicer is using the TableName[Period] column in the slicer.

If you want to make the measure independent of any slicers, I think you could replace ALL(TableName[Period]) with just ALL() in the DAX code.

[Charline_74]

Thanks for your reply, but it's not working

[frithjof_v]

Did you try Edit interactions, or did you try inserting ALL() in the measure?