cancel
Showing results for
Did you mean:
Member

## 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
Member

## 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)```

Member

## 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

## 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
Member

## 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)```

Member

## 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
Member

## 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

Member

## 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

## 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 !

Member

## 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