Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I ran into an interesting issue where I need previous balance from last reported month but PrevoiusBalance DAX won't work because Date reported are not in continuous order. How can I get this to work, Please see screenshot, Any help would be greatly appreciated.
Solved! Go to Solution.
Hi @Rahulsingh,
Based on my test, you could refer to below steps:
Sample data:
Create two measures:
Index = RANKX(ALL(Table1),FIRSTNONBLANK('Table1'[Dates],'Table1'[Dates]),,ASC,Dense)
Measure = var a=[Index]-1 return CALCULATE(SUM(Table1[Balance]),FILTER(ALL('Table1'),'Table1'[Index]=a))
Result:
You could also download the pbix file to have a view:
https://www.dropbox.com/s/vgkl8c3yf5vccn0/Previous%20Balance%20Calculation%20Issue.pbix?dl=0
Regards,
Daniel He
Hi @Rahulsingh,
Based on my test, you could refer to below steps:
Sample data:
Create two measures:
Index = RANKX(ALL(Table1),FIRSTNONBLANK('Table1'[Dates],'Table1'[Dates]),,ASC,Dense)
Measure = var a=[Index]-1 return CALCULATE(SUM(Table1[Balance]),FILTER(ALL('Table1'),'Table1'[Index]=a))
Result:
You could also download the pbix file to have a view:
https://www.dropbox.com/s/vgkl8c3yf5vccn0/Previous%20Balance%20Calculation%20Issue.pbix?dl=0
Regards,
Daniel He
Hi,
Share the link from where i can download your PBI file. Please also show the expected result there.
I'd suggest using an index column in Power Query, then writing a DAX formula measure to get the value of the Index minus 1.
https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
Last Value = var indexValue = MAX('YourTable'[Index]) RETURN IF( indexValue > 1, CALCULATE( SUM('YourTable'[Balance]), ALL('YourTable'), 'YourTable'[Index] = (indexValue - 1) ), 0 )
Hi @Anonymous
what does INDEX > 1 in your formula stand? When I use INDEX > 1 it's giving me an error
@Rahulsingh that should have been indexValue it represents an error trap for the occasion of where this calcules for the first row in the data (index = 1). That was just a silly typo on my part. I've updated the code.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |