Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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, 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....

example_data.png

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:

desired_outcome.png

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 

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

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 )
    )

3.PNG

 


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.

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.

View solution in original post

4 REPLIES 4
v-lid-msft
Community Support
Community Support

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 )
    )

3.PNG

 


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.

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
Not applicable

Hi @lc_finance  & @v-lid-msft ,

 

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

Hi @Anonymous ,

 

 

I'm very glad it helped you! 

 

Best of luck for your analysis

 

LC

lc_finance
Solution Sage
Solution Sage

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:

Last change per category.png

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.