How to find the delta between the period when the interval is not consistent?

[Anonymous]

I need to calculate the different of the data of the current period vs. the previous period to show the growth. There is no exact  interval between period, for example, there could be a week, 5 days or 4 days between the periods. Example of the data is below:

	4/18/20	4/25/20	4/30/20	5/4/20
A	100	108	150	210
B	200	180	230	250
C	300	290	310	320

Please suggest DAX calculation to be used for the measure. Thanks in advance for your help!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

You may create two measures as below.

Value for last date = 
var _category = SELECTEDVALUE('Table'[Category])
var _date = SELECTEDVALUE('Table'[Date])
var _lastdate = 
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        ALL('Table'),
        'Table'[Category] = _category&&
        'Table'[Date]<_date
    )
)
var _result = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Date] = _lastdate&&
        'Table'[Category] = _category
    )
)
return
IF(
    NOT(ISBLANK(_lastdate)),
    _result
)

Growth = 
DIVIDE(
    SELECTEDVALUE('Table'[Value])-[Value for last date],
    [Value for last date]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-alq-msft This works really well. Thank you for your help!

[mahoneypat]

First thing would be to unpivot your data, so you have ABC column, Date, and value.  I would also sort your data by Date descending and then add an index column, to make your DAX calculation easier.  Something like:

Difference from Previous Period = var currentperiod = sum(Table[Value]) //even if a single row, still need to aggregate it

var currentindex = selectedvalue(Table[Index])

var prevperiod = calculate(sum(Table[Value]), all(Table), Table[Index] = currentindex-1

return currentperiod - prevperiod

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi @mahoneypat - I'm not sure if the index column will work since I actually have duplicate values in the Date column since we collect data multple times in a day. So I would actually need to aggregate all the data each day first before applying index column? Not sure how that work?

Thanks!

[amitchandak]

@Anonymous , Rank can help you.  Create incr Rank on your period.

Period Rank = RANKX(all('Date'),'Date'[Period Start date],,ASC,Dense)

This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1))

Created a small video on that -https://www.dropbox.com/s/1pffei8yh64xwey/MS_conf1.mp4?dl=0

[Anonymous]

Hi @amitchandak - I'm trying your solution with RANKX and got this error

"A single value for column 'Date' in the table 'XYZ' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max count or sum to get a single result."

This is how my RANKX looks like

RANKX(ALL(Table[Date]), Table[Date], ,ASC, Dense)

Our actual table has data that comes in multiple times in a day, for example I may have 100 counts for 'A' on 4/18 at 9 am and 200 counts for 'A' again at 2 pm. Date column was converted from datetime data type into date. So I think I'm getting the error because Date column used as an expression has multiple value? How should I resolve this?

Thank you!