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
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
Solved! Go to 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.
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.
Hi @lc_finance & @v-lid-msft ,
Thank you both so much for the explanations and example files! You helped immensily
Hi @Anonymous ,
I'm very glad it helped you!
Best of luck for your analysis
LC
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
136 | |
111 | |
98 | |
97 | |
92 |