Previous Day Value

[MHTANK]

Date	VALUE
01-11-2024	126
02-11-2024	494
08-11-2024	78
08-11-2024	88
25-11-2024	98
30-11-2024	108
02-12-2024	118
03-12-2024	128
06-12-2024	100
06-12-2024	90
28-12-2024	200
31-12-2024	110
01-01-2025	20
02-01-2025	84
03-01-2025	145
07-01-2025	25

This is my Data.

From that I want to create one matrix with previous day value.

i.e.,

Here,

TOTAL_VALUE = SUM(Sheet1[VALUE])

Prev_Value = CALCULATE([TOTAL_VALUE], Sheet1[Date].[Day] = MAX(Sheet1[Date].[Day])-1)

I got this aready, But problem is 

the Date are not continous, there is a different gaps between two dates. Because of this 1st problem occurs.

For example, here no value for 03-11-2024 but there is Prev_value come, but I want value of 02-11-2024 in 08-11-2024.

And 2nd problem is that there is blank value come for every 1st day of month, but there I want value of last day of previous month.

For example, in prev_value of 02-12-2024 I want value of 30-11-2024 .

So, please give me solutoin, How I can achieve this?

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[MHTANK]

Thank you so much. 👍🏻

[Ashish_Mathur]

You are welcome.

[ThxAlot]

PREV_LASTNONBLANK = 
VAR __prev =
    CALCULATE( LASTNONBLANK( DATA[Date], 0 ), DATA[Date] < MAX( DATA[Date] ) )
RETURN
    CALCULATE( [SUM Value], DATA[Date] = __prev )

PREV_OFFSET = 
CALCULATE( [SUM Value], OFFSET( -1, ALLSELECTED( DATA[Date] ) ) )

[MHTANK]

Yes, this is work 👍🏻 in this data.

But in my original data (company's data) this is not work for the starting day of month. Can you please tell me what problems may be happening?

[lbendlin]

But problem is 
the Date are not continous, there is a different gaps between two dates

I have been using OFFSET(-1) in such scenarios recently, and I really like it. Maybe give it a try.

[MHTANK]

This is not work.