cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
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:

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.

12 REPLIES 12
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:

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.

Helper III

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)

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:

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.

Helper III

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!

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?

Super User

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.

Helper III

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.

Super User

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.

Helper III

here's a link to the sample data:

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.

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:

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.

Helper III

Maybe it also has to do with this measure?:

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

Helper III

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)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors