Previous Row record in matrix - dynamic

[Uzi2019]

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

[v-zhangti · Accepted solution]

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.