Forum Discussion
Community ChampionPrevious Row record in matrix - dynamic
Hi Experts,
I have one matrix which will look like below, In that I want to compute Previous Day Amount. But When I drilldown to only Month so it would show Previous Month in the same matrix and DAX.
Brand | Order ID | Start Date | End Date | Order Date | Amount |
AAA | 1234567 | 24-Apr-23 | 01-May-23 | 24-Apr-23 | 100 |
AAA | 1234567 | 24-Apr-23 | 01-May-23 | 25-Apr-23 | 200 |
AAA | 1234567 | 24-Apr-23 | 01-May-23 | 26-Apr-23 | 140 |
AAA | 1234567 | 24-Apr-23 | 01-May-23 | 27-Apr-23 | 200 |
AAA | 1234567 | 24-Apr-23 | 01-May-23 | 28-Apr-23 | 100 |
AAA | 1234567 | 24-Apr-23 | 01-May-23 | 29-Apr-23 | 120 |
AAA | 1234567 | 24-Apr-23 | 01-May-23 | 30-Apr-23 | 210 |
AAA | 1234567 | 24-Apr-23 | 01-May-23 | 01-May-23 | 100 |
Expected Output: (When we drill up to till Month so In May column the sum of April should get displayed.)
Hi, Uzi2019
You can try the following methods.
Previous Sum = Var _Sum=CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),MONTH([Order Date])=SELECTEDVALUE('Table'[Order Date].[MonthNo])-1))+0 Return IF(SUM('Table'[Amount])=BLANK(),BLANK(),_Sum)Output = IF(HASONEVALUE('Table'[Order Date].[Day]),SUM('Table'[Amount]),[Previous Sum])Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
v-zhangtiCommunity Support
Hi, Uzi2019
You can try the following methods.
Previous Sum = Var _Sum=CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),MONTH([Order Date])=SELECTEDVALUE('Table'[Order Date].[MonthNo])-1))+0 Return IF(SUM('Table'[Amount])=BLANK(),BLANK(),_Sum)Output = IF(HASONEVALUE('Table'[Order Date].[Day]),SUM('Table'[Amount]),[Previous Sum])Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.