Add one month to a year with condition

[AsNa_92]

Hi Guys!

I have senario where the user should choose a date from slicer, then based on the year selected I want to add one more month and equal the year of selected date to a year of another date year.

I have one table that has 2 columns of dates (example, Date1 and Date2)

Date1 will be as selected date (in a slicers dropdown)

Date2 will be assigned in a table

If user selected from slicer: December2023 

Date1+ one month = January 2024 (new Date1)

So the new Date1= 2024 should be equal to Date2= 2024 (same year)

I tried to create a measure like this but I'm stuck is it correct or where do I place it or how to check ?? because when I put it in a table it shows error:

NextMonth =

VAR PreviousMonths = DATEADD(Table[DATE1].[Date],1, MONTH)

RETURN

   YEAR(PreviousMonths) = YEAR(max([Date2]))

[AsNa_92 · Accepted solution]

No worries, I found the solution for that!

create a measure as below:

FilteredRecords =

var SelectedYear=YEAR(SELECTEDVALUE(Table[DATE1]))

var SelectedMonth=MONTH(SELECTEDVALUE(Table[DATE1]))

var YearAdjustment=

if(SelectedMonth=12, //if december

1, //add one year

0 //else no change )

return CALCULATE( countrows(Table), filter(Table, (Table[DATE2].[Year]=(SelectedYear+YearAdjustment))))  

Then apply it as a filter on the table and choose greater than or equal 1.

[Sergii24]

Hi @AsNa_92, I'm reffering to your example:

If user selected from slicer: December2023 

Date1+ one month = January 2024 (new Date1)

So the new Date1= 2024 should be equal to Date2= 2024 (same year)

So there will be only 1 slicer on the page (Date1), from which a user can selecte month-year (Nov-23, Dec-24, Jan-24 and etc.). Once the selection is done I suppose you want to perform some calcualtion and show the result on a visual (can you provide an example of output you expect?).

The objective is to perform a calculation in another filter context (replace selected date filter with the following month). Is it correct?

Coming to the phrase: "So the new Date1= 2024 should be equal to Date2= 2024 (same year)", I don't understand what you want to achieve... If you selected Date1 as Dec-23, year of Date1 can't be 2024, it will remain 2023. What you can do is to use value 2024 in calculation of some KPI. Please elaborate more on your reqeust my assumptions above are incomplete or wrong. 

[AsNa_92]

Hi Sergi,

so here is a screenshot of what I want to show + the sample data:

COUNTRY	GEOMAPPING	DIFFERENCE	REMAINING	BUDGET	CURRENCY	FUTURE_DATE	DATE
IN	Asia	0	55,244,374	54,860,000	USD	29-Mar-2024	31-Dec-23
IL	Asia	3.057	493,877,287	526,173,000	OTHERS	30-Apr-2025	31-Dec-23
US	North America	0.858	177,087,947	178,693,000	USD	2-Apr-2026	31-Dec-23
AU	Rest of the World	0.15	367,718,026	369,305,000	USD	22-Nov-2024	31-Jan-24
CN	Asia	0	37,821,665	35,684,000	USD	12-Sep-2025	31-Jan-24
KR	Asia	20388.603	26,747,000	26,747,000	USD	9-Aug-2026	31-Jan-24
US	North America	0.07	366,722,388	363,555,000	USD	31-Aug-2024	29-Feb-24
KW	Other GCC	0.27	130,135,546	130,318,000	USD	18-May-2025	29-Feb-24
CA	Rest of the World	0.95	34,483,252	34,678,000	USD	3-Aug-2026	29-Feb-24