Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate difference between values per category at irregularly spaced dates

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,...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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.

lc_finance's avatar
lc_finance
Solution Sage
6 years ago

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