Forum Discussion
Solved
Previous Day Value
| 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?
Hi,
PBI file attached.
Hope this helps.
7 Replies
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] ) ) )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?
Hi,
PBI file attached.
Hope this helps.
Thank you so much. 👍🏻
You are welcome.
But problem is the Date are not continous, there is a different gaps between two datesI have been using OFFSET(-1) in such scenarios recently, and I really like it. Maybe give it a try.
This is not work.
