cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gururajv007
Helper I
Helper I

Output as per the date Criteria

Expert Advice needed please : Help me to get the OutPut as per the state & end date critieria.

Looking at the "Start Date" & "End Date", duration between the month as per the critiria in column D, got the column "C" output.

Note : Fiscal Year Starts from Oct'19 to Sept'20

start dateEnd dateNumber of months in scope for FY20 ( OUTPUT NEEDED )Remarks to understand better.
Jul-2018Jun-190Not in current Fiscal year
Aug-2018Jul-190Not in current Fiscal year
Sep-2018Aug-190Not in current Fiscal year
Oct-2018Sep-190Not in current Fiscal year
Nov-2018Oct-1911- Month from fiscal year
Dec-2018Nov-1922- Months from fiscal year
Jan-2019Dec-1933- Months from fiscal year
Feb-2019Jan-2044- Months from fiscal year
Mar-2019Feb-2055- Months from fiscal year
Apr-2019Mar-2066- Months from fiscal year
May-2019Apr-2077- Months from fiscal year
Jun-2019May-2088- Months from fiscal year
Jul-2019Jun-2099- Months from fiscal year
Aug-2019Jul-201010- Months from fiscal year
Sep-2019Aug-201111- Months from fiscal year
Oct-2019Sep-201212- Months from fiscal year
Nov-2019Oct-201111 - Months are due to reach fiscal year Oct'20 from Start date
Dec-2019Nov-201010 Months are due to reach fiscal year Oct'20 from Start Date
Jan-2020Dec-2099 Months are due to reach fiscal year Oct'20 from Start Date
Feb-2020Jan-2188 Months are due to reach fiscal year Oct'20 from Start Date
Mar-2020Feb-2177 Months are due to reach fiscal year Oct'20 from Start Date
Apr-2020Mar-2166 Months are due to reach fiscal year Oct'20 from Start Date
May-2020Apr-2155 Months are due to reach fiscal year Oct'20 from Start Date
Jun-2020May-2144 Months are due to reach fiscal year Oct'20 from Start Date
Jul-2020Jun-2133 Months are due to reach fiscal year Oct'20 from Start Date
Aug-2020Jul-2122 Months are due to reach fiscal year Oct'20 from Start Date
Sep-2020Aug-2111 Months are due to reach fiscal year Oct'20 from Start Date
Oct-2020Sep-210Not in current Fiscal year
Nov-2020Oct-210Not in current Fiscal year
Dec-2020Nov-210Not in current Fiscal year
1 ACCEPTED SOLUTION

Hi @Gururajv007 ,

 

Update the formula as below.

Output needed = 
var yt = YEAR(TODAY())
var yt1 = IF(MONTH(TODAY())<=10,yt,yt+1)
VAR currfisysdate =
    DATE ( yt1-1, 10, 01 )
VAR currfisyedate =
    DATE ( yt1, 09, 01 )
VAR fis =
    DATE ( yt1, 10, 01 )
RETURN
    IF (
        'Table'[End date] < currfisysdate
            || 'Table'[Start date] > currfisyedate,
        0,
        IF (
            'Table'[End date] >= currfisysdate
                && 'Table'[End date] <= currfisyedate,
            DATEDIFF ( currfisysdate, 'Table'[End date],MONTH)+1,
            DATEDIFF ( 'Table'[Start date], fis, MONTH )
        )
    )

 

BTW, We can get the status column by the formula.

Status = 
VAR yt =
    YEAR ( TODAY () )
VAR yt1 =
    IF ( MONTH ( TODAY () ) <= 10, yt, yt + 1 )
VAR currfisysdate =
    DATE ( yt1 - 1, 10, 01 )
VAR currfisyedate =
    DATE ( yt1, 09, 01 )
VAR fis =
    DATE ( yt1, 10, 01 )
RETURN
    IF (
        'Table'[End date] >= currfisysdate
            && 'Table'[End date] <= currfisyedate,
        "Looks this reversed",
        "correct"
    )

date.PNG

 

PBIX as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
v-frfei-msft
Community Support
Community Support

Hi @Gururajv007 ,

 

Please create a calcualted column as below.

Column = 
var yt = YEAR(TODAY())
var yt1 = IF(MONTH(TODAY())<=10,yt,yt+1)
VAR currfisysdate =
    DATE ( yt1-1, 10, 01 )
VAR currfisyedate =
    DATE ( yt1, 09, 01 )
VAR fis =
    DATE ( yt1, 10, 01 )
RETURN
    IF (
        'Table'[End date] < currfisysdate
            || 'Table'[Start date] > currfisyedate,
        0,
        IF (
            'Table'[End date] >= currfisysdate
                && 'Table'[End date] <= currfisyedate,
            DATEDIFF ( 'Table'[End date], currfisyedate, MONTH ) + 1,
            DATEDIFF ( 'Table'[Start date], fis, MONTH )
        )
    )

Capture.PNG 

 

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Thank you so much your support & appriciated. But small criteria is missing.

 Will be great if you could help me to sort it out. Pl refer the "Output needed"column

 

Start dateEnd dateColumnOutput neededStatus
1-Jul-181-Jun-1900Correct
1-Aug-181-Jul-1900Correct
1-Sep-181-Aug-1900Correct
1-Oct-181-Sep-1900Correct
1-Nov-181-Oct-19121Looks this reversed
1-Dec-181-Nov-19112Looks this reversed
1-Jan-191-Dec-19103Looks this reversed
1-Feb-191-Jan-2094Looks this reversed
1-Mar-191-Feb-2085Looks this reversed
1-Apr-191-Mar-2076Looks this reversed
1-May-191-Apr-2067Looks this reversed
1-Jun-191-May-2058Looks this reversed
1-Jul-191-Jun-2049Looks this reversed
1-Aug-191-Jul-20310Looks this reversed
1-Sep-191-Aug-20211Looks this reversed
1-Oct-191-Sep-20112Looks this reversed
1-Nov-191-Oct-201111Correct
1-Dec-191-Nov-201010Correct
1-Jan-201-Dec-2099Correct
1-Feb-201-Jan-2188Correct
1-Mar-201-Feb-2177Correct
1-Apr-201-Mar-2166Correct
1-May-201-Apr-2155Correct
1-Jun-201-May-2144Correct
1-Jul-201-Jun-2133Correct
1-Aug-201-Jul-2122Correct
1-Sep-201-Aug-2111Correct
1-Oct-201-Sep-2100Correct
1-Nov-201-Oct-2100Correct
1-Dec-201-Nov-2100Correct

Hi @Gururajv007 ,

 

Update the formula as below.

Output needed = 
var yt = YEAR(TODAY())
var yt1 = IF(MONTH(TODAY())<=10,yt,yt+1)
VAR currfisysdate =
    DATE ( yt1-1, 10, 01 )
VAR currfisyedate =
    DATE ( yt1, 09, 01 )
VAR fis =
    DATE ( yt1, 10, 01 )
RETURN
    IF (
        'Table'[End date] < currfisysdate
            || 'Table'[Start date] > currfisyedate,
        0,
        IF (
            'Table'[End date] >= currfisysdate
                && 'Table'[End date] <= currfisyedate,
            DATEDIFF ( currfisysdate, 'Table'[End date],MONTH)+1,
            DATEDIFF ( 'Table'[Start date], fis, MONTH )
        )
    )

 

BTW, We can get the status column by the formula.

Status = 
VAR yt =
    YEAR ( TODAY () )
VAR yt1 =
    IF ( MONTH ( TODAY () ) <= 10, yt, yt + 1 )
VAR currfisysdate =
    DATE ( yt1 - 1, 10, 01 )
VAR currfisyedate =
    DATE ( yt1, 09, 01 )
VAR fis =
    DATE ( yt1, 10, 01 )
RETURN
    IF (
        'Table'[End date] >= currfisysdate
            && 'Table'[End date] <= currfisyedate,
        "Looks this reversed",
        "correct"
    )

date.PNG

 

PBIX as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors