cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bvss Helper III
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)

 

StartStart_MonthEndEnd DateEnd_MonthEndFY END
6/12/20186/30/2018  3/31/2020
10/9/201810/31/2018  3/31/2020
10/11/201710/31/2017  3/31/2020
12/3/201812/31/2018  3/31/2020
9/18/20179/30/2017  3/31/2020
6/25/20186/30/2018  3/31/2020
2/5/20192/28/20194/22/20194/30/20193/31/2020
10/2/201710/31/2017  3/31/2020
12/5/201812/31/2018  3/31/2020
3/1/20103/31/2010  3/31/2020
4/22/20194/30/2019  3/31/2020
12/11/201812/31/20186/7/20196/30/20193/31/2020
9/10/20189/30/2018  3/31/2020
7/6/20157/31/2015  3/31/2020
2/1/20162/29/2016  3/31/2020
11/13/201711/30/2017  3/31/2020
5/14/20185/31/20185/24/20195/31/20193/31/2020
1/24/20191/31/20198/15/20198/31/20193/31/2020
6/18/20186/30/20186/4/20196/30/20193/31/2020
8/20/20188/31/2018  3/31/2020
12/1/201612/31/2016  3/31/2020
10/22/201810/31/2018  3/31/2020

 

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

Please help me out from this

 

Thank you in advance

 

Regards

B V S S

3 ACCEPTED SOLUTIONS

Accepted Solutions
Mvignesh53 Resolver IV
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)

 

View solution in original post

Highlighted
Mvignesh53 Resolver IV
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.

View solution in original post

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





View solution in original post

6 REPLIES 6
Mvignesh53 Resolver IV
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)

 

View solution in original post

bvss Helper III
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
 
Capture.JPG
 
Thank you in advance
 
Regards,
B V S S
bvss Helper III
Helper III

Re: How to show number of months between two dates

Hi @Mvignesh53,

 

Calculated column or measure anything is okay for me

 

Thank you in advance

 

Regards,

B V S S

Highlighted
Mvignesh53 Resolver IV
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.

View solution in original post

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





View solution in original post

bvss Helper III
Helper III

Re: How to show number of months between two dates

HI @Mvignesh53 , @PattemManohar ,

 

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

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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.

Top Solution Authors
Top Kudoed Authors