Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
S3
Helper III
Helper III

PreviousDay for First Date in Dataset

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) )
1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1666257219288.png


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.

View solution in original post

12 REPLIES 12
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1666257219288.png


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.

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
Community Support
Community Support

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:

vyalanwumsft_0-1666172680742.png

vyalanwumsft_1-1666172697085.png


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.

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!E16B6567-5537-4341-B320-1A19384C0F9D.jpeg

S3
Helper III
Helper III

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? 

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.

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. 

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.


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. 

5D335456-DC9D-41EC-8BC5-1CD71A5F57F4.jpegNA.JPG




v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1665730374956.png
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.

 

Maybe it also has to do with this measure?:

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

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

2BDA5593-8C54-4302-9016-91D5EE350EF4.jpeg
- I was also wishing for a previoud period value (same number of days before the selected period began) 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.