Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

Anonymous
Not applicable

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

PattemManohar
Community Champion
Community Champion

@Anonymous  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 PBI Community Champion




View solution in original post

6 REPLIES 6
Anonymous
Not applicable

HI @Anonymous , @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

PattemManohar
Community Champion
Community Champion

@Anonymous  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 PBI Community Champion




Anonymous
Not applicable

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)

 

Anonymous
Not applicable

Hi @Anonymous,

 

Calculated column or measure anything is okay for me

 

Thank you in advance

 

Regards,

B V S S

Anonymous
Not applicable

Hi @Anonymous ,

 

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
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.