cancel
Showing results for
Did you mean:
Helper III

How to show number of months between two dates

Hi Community,

Here, I have date columns in my table, and I have one FY Year Slicer also now i want to show

1. No.of months between End_MonthEnd and FY END (if End_MonthEnd is Blank then No.of months columns needs to show Zero else No.of months should come)

2.  No.of months between Start_MonthEnd and FY END  (if Year(Start_MonthEnd) = Slicer Slected FY Year then No.of months columns needs to show else No.of months columns needs to show Zero)

 Start Start_MonthEnd End Date End_MonthEnd FY END 6/12/2018 6/30/2018 3/31/2020 10/9/2018 10/31/2018 3/31/2020 10/11/2017 10/31/2017 3/31/2020 12/3/2018 12/31/2018 3/31/2020 9/18/2017 9/30/2017 3/31/2020 6/25/2018 6/30/2018 3/31/2020 2/5/2019 2/28/2019 4/22/2019 4/30/2019 3/31/2020 10/2/2017 10/31/2017 3/31/2020 12/5/2018 12/31/2018 3/31/2020 3/1/2010 3/31/2010 3/31/2020 4/22/2019 4/30/2019 3/31/2020 12/11/2018 12/31/2018 6/7/2019 6/30/2019 3/31/2020 9/10/2018 9/30/2018 3/31/2020 7/6/2015 7/31/2015 3/31/2020 2/1/2016 2/29/2016 3/31/2020 11/13/2017 11/30/2017 3/31/2020 5/14/2018 5/31/2018 5/24/2019 5/31/2019 3/31/2020 1/24/2019 1/31/2019 8/15/2019 8/31/2019 3/31/2020 6/18/2018 6/30/2018 6/4/2019 6/30/2019 3/31/2020 8/20/2018 8/31/2018 3/31/2020 12/1/2016 12/31/2016 3/31/2020 10/22/2018 10/31/2018 3/31/2020

I tried Datediff.. But i am not getting proper result

Regards

B V S S

3 ACCEPTED SOLUTIONS

Accepted Solutions
Resolver IV

Re: How to show number of months between two dates

Hi,

I assume that you want to Create this as a Columns. One thing is that You cant pass the Slicer value into the Table Column. A Slicer Value can be Accessed using SELECTEDVALUE() DAX function, but this can be used only with a Measure. If you want to show this as a part of the Table Visual rather than Table in Modelling, then you could use the below function. Please note that the Numbers indicate the answers to your questions.

```Selected Month := VAR Selection_Year = SELECTEDVALUE(Table[Date].Year)
RETURN <<Return Table Expression with Selection_Year Filter>> ```

1.

```NOM_EndDate_FYEND:=
IF(Table[End Date]<> BLANK(),DATEDIFF(Table[End Date], Table[FY END], Month),0)```

2.

Spoiler
```NOM_StartDate_FYEND:=
IF(Table[End Date]<> BLANK(),DATEDIFF(Table[Start Date], Table[FY END], Month),0)```

Resolver IV

Re: How to show number of months between two dates

`IF(Query1[End_MonthEnd]<> BLANK(),DATEDIFF(DATE(YEAR(Query1[End_MonthEnd]),MONTH(Query1[End_MonthEnd]),DAY(Query1[End_MonthEnd])),DATE(YEAR(Query1[FY END]),MONTH(Query1[FY END]),DAY(Query1[FY END])), Month),0)`

Can you try with the above expression. Also, try to Resolve the Month(),Year(),Date() expressions as new columns to check if this is resolving properly if there are errors.

Best Regards,
Vignesh M

If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue.

Super User I

Re: How to show number of months between two dates

@bvss  Please try this as "New Column"

```No.of Months =
VAR _Option1 = IF(Test302MonthsBtwn[End_MonthEnd]=BLANK(),0,DATEDIFF(DATEVALUE(Test302MonthsBtwn[End_MonthEnd]),Test302MonthsBtwn[FY END],MONTH))
RETURN _Option1```
```No.of Months 2 =
VAR _Option2 = IF(YEAR(DATEVALUE(Test302MonthsBtwn[Start_MonthEnd]))=YEAR(Test302MonthsBtwn[FY END]),DATEDIFF(DATEVALUE(Test302MonthsBtwn[Start_MonthEnd]),Test302MonthsBtwn[FY END],MONTH),0)
RETURN _Option2```

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

6 REPLIES 6
Resolver IV

Re: How to show number of months between two dates

Hi,

I assume that you want to Create this as a Columns. One thing is that You cant pass the Slicer value into the Table Column. A Slicer Value can be Accessed using SELECTEDVALUE() DAX function, but this can be used only with a Measure. If you want to show this as a part of the Table Visual rather than Table in Modelling, then you could use the below function. Please note that the Numbers indicate the answers to your questions.

```Selected Month := VAR Selection_Year = SELECTEDVALUE(Table[Date].Year)
RETURN <<Return Table Expression with Selection_Year Filter>> ```

1.

```NOM_EndDate_FYEND:=
IF(Table[End Date]<> BLANK(),DATEDIFF(Table[End Date], Table[FY END], Month),0)```

2.

Spoiler
```NOM_StartDate_FYEND:=
IF(Table[End Date]<> BLANK(),DATEDIFF(Table[Start Date], Table[FY END], Month),0)```

Helper III

Re: How to show number of months between two dates

Hi @Mvignesh53 ,

Thank you for your quick response...

I tried with this

IF(Query1[End_MonthEnd]<> BLANK(),DATEDIFF(Query1[End_MonthEnd], Query1[FY END], Month),0)

But I am not getting proper value... For Blanks It is giving Zero But Non blank it's not giving proper result.
See Belo image

Regards,
B V S S
Helper III

Re: How to show number of months between two dates

Hi @Mvignesh53,

Calculated column or measure anything is okay for me

Regards,

B V S S

Resolver IV

Re: How to show number of months between two dates

`IF(Query1[End_MonthEnd]<> BLANK(),DATEDIFF(DATE(YEAR(Query1[End_MonthEnd]),MONTH(Query1[End_MonthEnd]),DAY(Query1[End_MonthEnd])),DATE(YEAR(Query1[FY END]),MONTH(Query1[FY END]),DAY(Query1[FY END])), Month),0)`

Can you try with the above expression. Also, try to Resolve the Month(),Year(),Date() expressions as new columns to check if this is resolving properly if there are errors.

Best Regards,
Vignesh M

If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue.

Super User I

Re: How to show number of months between two dates

@bvss  Please try this as "New Column"

```No.of Months =
VAR _Option1 = IF(Test302MonthsBtwn[End_MonthEnd]=BLANK(),0,DATEDIFF(DATEVALUE(Test302MonthsBtwn[End_MonthEnd]),Test302MonthsBtwn[FY END],MONTH))
RETURN _Option1```
```No.of Months 2 =
VAR _Option2 = IF(YEAR(DATEVALUE(Test302MonthsBtwn[Start_MonthEnd]))=YEAR(Test302MonthsBtwn[FY END]),DATEDIFF(DATEVALUE(Test302MonthsBtwn[Start_MonthEnd]),Test302MonthsBtwn[FY END],MONTH),0)
RETURN _Option2```

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Highlighted
Helper III

Re: How to show number of months between two dates

Both of your solutions are working properly.. I made small mistake that's why previously it shows wrong values

Thank you for your help guys

Regards,

B V S S

Announcements

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors