Calculate difference between values per category at irregularly spaced dates

[Anonymous]

Hi PowerBi Community,

Background: I'm just learning PowerBI (it's a great tool!) and hoping you all can help me. I have found posts on the form about regularly spaced time interval comparisons (YoY, etc) but I failed to modify them to work for my specific case.

Problem: For a given category, I would like to determine the difference between the most recent value and the previous value.

My data is not recorded at regular intervals so the time between two readings for any given category is irregularly spaced. I included a screenshot of some example data that I made in excel below. The right most column 'Change' is my first desired outcome. In PowerBI while trying to find a solution, I managed to create a calculated column like the "IndexCategory" seen below, that is a date rank by category. I think this column can help me....

And actually, for my purposes, I just need the last "Change" value for each category, so my ultimate desired outcome would be a PowerBI Table showing:

Would you recommend I create a calculated column "Change" and then a measure based on the change column that would return the most recent Change value for each category? I'm still a DAX newbie so it would be great if anyone could help spell out the syntax for me 🙂

Thank you!

And here is a link to a wetransfer with the excel pictured above 

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create measure use following formula to meet your requirement:

LastChange = 
VAR LastDay =
    MAX ( 'Table'[Date] )
VAR LastTwoday =
    CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( 'Table', [Date] < LastDay ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[MyValue] ),
        FILTER ( 'Table', 'Table'[Date] = LastDay )
    )
        - CALCULATE (
            SUM ( 'Table'[MyValue] ),
            FILTER ( 'Table', 'Table'[Date] = LastTwoday )
        )

If use your index column, this formula can be more easier:

LastChangeUseIndex = 
CALCULATE (
    SUM ( 'Table'[MyValue] ),
    FILTER ( 'Table', 'Table'[IndexCategory] = MAX ( 'Table'[IndexCategory] ) )
)
    - CALCULATE (
        SUM ( 'Table'[MyValue] ),
        FILTER ( 'Table', 'Table'[IndexCategory] = MAX ( 'Table'[IndexCategory] ) - 1 )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @lc_finance  & @v-lid-msft ,

Thank you both so much for the explanations and example files! You helped immensily 

[lc_finance]

Hi @Anonymous ,

I'm very glad it helped you! 

Best of luck for your analysis

LC

[lc_finance]

Hi @Anonymous ,

you can do this with a calculated table.

Here is a link to the Power BI file for my proposed solution.

You can create a calculated table by clicking on Modeling -> New Table.

Here is the DAX formula for it:

Last Change = 
ADDCOLUMNS(
    VALUES('Sheet1'[Category]),
    "Last change" ,
    VAR currentCategory = [Category]
    VAR maxDate = MAXX(FILTER('Sheet1','Sheet1'[Category]=currentCategory),[Date])
    VAR dateBeforeMax = MAXX(FILTER('Sheet1',AND('Sheet1'[Category]=currentCategory, 'Sheet1'[Date]<maxDate)),[Date])
    VAR change = LOOKUPVALUE('Sheet1'[MyValue],Sheet1[Category],currentCategory,Sheet1[Date],maxDate) -
    LOOKUPVALUE('Sheet1'[MyValue],Sheet1[Category],currentCategory,Sheet1[Date],dateBeforeMax)
    RETURN change
)

And below is the explanation.

First I add new rows to the table, one for each value of category (one row for apple, one for banana and one for cherry). The formula VALUES takes care of this.

Second, I add a new column called 'last change'. Last change is calculated with the help of a few intermediary variables:

- 'currentCategory' corresponds to the category (apple, banana, cherry)

- 'maxDate' returns the last date for the category (October 25th for apple)

- 'dateBeforeMaxDate' returns the date before the last for each category (October 22 for apple)

- 'change' uses LOOKUPVALUE to find the value for the last date, the value for the date before the last and subtracts them.

To finish, here is a screenshot:

Hope this helps you. Do not hesitate if you have further questions.

Regards,

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com