Forum Discussion

S3's avatar
S3
Helper III
3 years ago
Solved

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 previo...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
3 years ago

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's avatar
S3
Helper III
3 years ago

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!