PreviousDay for First Date in Dataset

[S3]

Hello, 

I have a dataset which begins on the 01st of January 2022 and ends on the 31st of July 2022. 

I also have a date slicer the user can choose from and a graph showing the value of previous period. 

It works fine for all days except for January 01st, there, it returns the value of July the 31st, concidering 31.07.2022 the previous day of 01.01.2022.

All I would like is to have it blank, since there is no data before 01.01.2022.

I would also like it to show me N/A if the time period I chose in that date range doesn't have an equivelent previous period. 

Here are my measures:

Start of Previous Period = PREVIOUSDAY(FIRSTDATE(DATEADD(Dates[Date],-1*[Days in This Period],DAY)))

End of Previous Period = PREVIOUSDAY(LASTDATE(DATEADD(Dates[Date],-1*[Days in This Period],DAY)))

Previous Period Value = CALCULATE([measure]

     ,DATESBETWEEN(

                                     Dates[Date],

                                     [Start of Previous Period],

                                  [End of Previous Period]),

                                     ALL(Dates) )

[v-yalanwu-msft · Accepted solution]

Hi, @S3 ;

Try it.

Previous Period Value = 
var _a= CALCULATE([Value Measure]
	                             ,DATESBETWEEN(
									 Dates[Date],
									 [Start of Previous Period],
				    				 [End of Previous Period]),
									 ALL(Dates))
var _diff2=DATEDIFF( CALCULATE(MIN('Dates'[Date]),ALLSELECTED(Dates)),CALCULATE(MAX('Dates'[Date]),ALLSELECTED(Dates)),DAY)
var _diff1=DATEDIFF( CALCULATE(MIN('Dates'[Date]),ALL(Dates)),CALCULATE(MIN('Dates'[Date]),ALLSELECTED(Dates)),DAY)
return IF(CALCULATE(SUM('Table'[value]),ALL('Table'))=_a||_diff1<_diff2,BLANK(), _a)

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[S3]

Hello @v-yalanwu-msft 

Thank youuu! I'm so happy about the solution! 

I also changed the Difference measure to show nothing as well, when the previous period is Blank (because then there's no difference, because there's no previous period. Here is the measure I added:

Difference  =

VAR _S =

[Value Measure] - 'Table'[Previous Period Value]

Return

IF (

'Table'[Previous Period Value]= Blank(), Blank(),_S)

[v-yalanwu-msft]

Hi, @S3 ;

Try to change it.

Previous Period Value = 
var _a= CALCULATE([Value Measure]
	                             ,DATESBETWEEN(
									 Dates[Date],
									 [Start of Previous Period],
				    				 [End of Previous Period]),
									 ALL(Dates))
return IF(CALCULATE(SUM('Table'[value]),ALL('Table'))=_a
||CALCULATE(MIN('Dates'[Date]),ALLSELECTED(Dates))=CALCULATE(MIN('Dates'[Date]),ALL(Dates)),BLANK(), _a)

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[S3]

Hello @v-yalanwu-msft, 

Thank so much, I really appreciate that it finally returns Blank for the day before the data begins.

The think is, I would like it to show Blank when any selected date has no Previous Data. Ex:
here in the picture, there are 197 days which are selected. There is no previous period for this selection, because the number of days before it is less than 197 days.

The Formula you gave me returns Blank only when the date selected begins from the first date of the data.

I appreciate your suggetsions a lot and I thank you!

[S3]

Hello @lbendlin, 

You've once helped me with a similar concept regarding previous month value. I've tried changing in your previous measure for this case, however I simply got the value of the same date and not the previous. 
This case also has an aditional point of having a selected period of time, and wishing to get N/A when the number of current period's days is greater than the number of the previous days'.

Do you have an idea about that please? 

[lbendlin]

Keep in mind that if you want to use time intelligence functions then your calendar table must be contiguous and must also cover the result of the calculation.

[S3]

Hello @lbendlin , 
thanks for your reply. I actually changed my previous Dataset from monthly to daily, so now I have all dates in my Calender. 

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

[S3]

here's a link to the sample data:

https://drive.google.com/file/d/1NKjo6Jp3-ZexOr8-pbstcmQuwjDNfu8l/view?usp=sharing


When one filters the dates between 01.01.2022 and 31.01.2022, it still shows a difference between this period and previous period, even though there's no previous period, and I would like it to show N/A or 0

The Previous Period Value is used only to calculate the Difference, if there's a way to show the real value of the previous period, that would also be very good. 

Thanks so much for replying. 

[v-yalanwu-msft]

Hi, @S3 ;

You could try it.

Previous Period Value = 
var _min=CALCULATE(MIN('Table'[Date]),ALLSELECTED('Table'))
var _max=CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))
return 
CALCULATE(SUM('Table'[value])
     ,FILTER(ALL('Table'),[Date]<_min&&[Date]>=_min-DATEDIFF(_min,_max,DAY)))

The final show:

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[S3]

Maybe it also has to do with this measure?:

Start of Previous Period = PREVIOUSDAY(FIRSTDATE(DATEADD(Dates[Date],-1*[Days in This Period],DAY)))

[S3]

Thank you for your reply. 

The file you've used is perfect to make my question clearer. When using the measure you've provided I get Blank on Dates where there's nothing before. However:

- we see here one value for all of the dates. What I meant was each day the value before it. So here for example, on 29.05.2022 I wanted to see the value of 28.05.2022

- I was also wishing for a previoud period value (same number of days before the selected period began)