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 ;

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

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)