Measure for earlier and later dates

[DavidNunes7]

Hello everybody

I'm having trouble creating a measure to meet the following requirements:

I have a table where there are the inspection records of places with the date and note I need to create a table visual where my measure will return me:

• For previous months that have no record, return the same value as the oldest recorded date;
• For subsequent months that have no records, return the same value as the most recently recorded date                                                            
• 

[Ashish_Mathur]

Hi,

Please find attached the PBI file.

Hope this helps.

[DavidNunes7]

Could you give me the measurement formula?
I can't open the file because your BI version is newer,

[Ashish_Mathur]

That will be way too many steps to list.  Please retry/try on another machine with an updated version.

[DavidNunes7]

Perfect!!!!
Thank You 🙂

[v-tangjie-msft · Accepted solution]

Hi @DavidNunes7 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a table and a measure. 

CALENDAR = ADDCOLUMNS(CALENDAR(DATE(2023,1,1),DATE(2023,12,31)),
"Month",FORMAT([Date],"MMMM"))

Measure 2 = var a=MAXX(FILTER(ALLSELECTED('Table'),[site name] in VALUES('Table'[site name])&&[Result]<>BLANK()),[Date])
var b=CALCULATE(SUM('Table'[Result]),FILTER('Table',EOMONTH('Table'[Date],0)=EOMONTH(MAX('CALENDAR'[Date]),0)))
var c=MINX(FILTER(ALLSELECTED('Table'),[site name] in VALUES('Table'[site name])&&[Result]<>BLANK()),[Date])
return IF(b<>BLANK(),b,IF(b=BLANK()&&MAX('CALENDAR'[Date])>a,CALCULATE(SUM('Table'[Result]),'Table'[Date]=a),IF(b=BLANK()&&MAX('CALENDAR'[Date])<C,CALCULATE(SUM('Table'[Result]),'Table'[Date]=C))))

(3) Then the result is as follows.

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. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.