Create a measure for next 12 months, but starting next month?

[Anonymous]

I have a column in my data table 'Effective Date' with date values like 3/15/2021. I can put this effective date field as a filter, and show only rows within next 12 months with relative date filtering... But it shows data within the current month, which I don't want

My objective: Create a date measure that will allow me to filter by next 12 months, but starting at the first of next month

Example: Today is 3/22/2021. Effective Date: > 4/1/2021 and on

[v-yetao1-msft · Accepted solution]

Hi @Anonymous ,

I probably understand what your needs, the following is a case that I did, you can use it as a reference .

(1)I create a table contains ‘Date’column. And calculate table ‘Date’ with the values from ‘Table’[Date]

Date = VALUES('Table'[Date])

(2)Use the table ‘Date’ as a slicer to filter data in another table.

(3)Create a measure to the table ‘Table’

Measure = CALCULATE(SUM('Table'[sale]),

          FILTER('Table',[Date]>=EOMONTH(SELECTEDVALUE('Date'[Date]),0)+1 && [Date]<=EDATE(EOMONTH(SELECTEDVALUE('Date'[Date]),0)+1,12)))

EOMONTH(SELECTEDVALUE('Date'[Date]),0)+1   is the first day of the next month of the date you selected

EDATE(EOMONTH(SELECTEDVALUE('Date'[Date]),0)+1,12)   is the first day of the next month of the date you selected plus 12 months

Use the above 2 times as the interval standard to filter the data of the table ‘Table’, you will get the result you want.

The effect is as shown:

Best Regards

Ailsa Tao

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if I wrongly understood.

I think you can use EOMonth function in your measure.

EOMONTH returns the date that in the end of date in the current month context.

So, if you can add 1 day, it will be always the first date of next month.

I hope it helped.

Thank you.

[Anonymous]

Something like this?

Future Effective Date = EOMONTH('Anaplan Approval Process Audit Log'[Effective Date],12)

Gives me an error for some reason: "A single value for column 'Effective Date' in table 'Anaplan Approval Process Audit Log' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

[Jihwan_Kim]

Hi, @Anonymous 

Sorry but if it is OK with you, can I see your sample pbix file?

Is it connected to the calendar table or not?

How is it connected?

I am not sure, but I think it depends on how your model looks like.

Please share the sample pbix file, and I can try to come up with the solution.

Thanks.