Skip to main content
cancel
Showing results for 
Search instead 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

Reply
AsNa_92
Helper I
Helper I

Add one month to a year with condition

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]))
1 ACCEPTED SOLUTION
AsNa_92
Helper I
Helper I

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.

 

 

View solution in original post

3 REPLIES 3
AsNa_92
Helper I
Helper I

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
Super User
Super User

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. 

Hi Sergi,

 

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

Untitled.png

 

COUNTRYGEOMAPPINGDIFFERENCEREMAININGBUDGETCURRENCYFUTURE_DATEDATE
IN Asia0               55,244,374           54,860,000USD29-Mar-202431-Dec-23
IL Asia3.057             493,877,287         526,173,000OTHERS30-Apr-202531-Dec-23
US North America0.858             177,087,947         178,693,000USD2-Apr-202631-Dec-23
AU Rest of the World0.15             367,718,026         369,305,000USD22-Nov-202431-Jan-24
CN Asia0               37,821,665           35,684,000USD12-Sep-202531-Jan-24
KR Asia20388.603               26,747,000           26,747,000USD9-Aug-202631-Jan-24
US North America0.07             366,722,388         363,555,000USD31-Aug-202429-Feb-24
KW Other GCC0.27             130,135,546         130,318,000USD18-May-202529-Feb-24
CA Rest of the World0.95               34,483,252           34,678,000USD3-Aug-202629-Feb-24

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors