Output as per the date Criteria

[Gururajv007]

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 date	End date	Number of months in scope for FY20 ( OUTPUT NEEDED )	Remarks to understand better.
Jul-2018	Jun-19	0	Not in current Fiscal year
Aug-2018	Jul-19	0	Not in current Fiscal year
Sep-2018	Aug-19	0	Not in current Fiscal year
Oct-2018	Sep-19	0	Not in current Fiscal year
Nov-2018	Oct-19	1	1- Month from fiscal year
Dec-2018	Nov-19	2	2- Months from fiscal year
Jan-2019	Dec-19	3	3- Months from fiscal year
Feb-2019	Jan-20	4	4- Months from fiscal year
Mar-2019	Feb-20	5	5- Months from fiscal year
Apr-2019	Mar-20	6	6- Months from fiscal year
May-2019	Apr-20	7	7- Months from fiscal year
Jun-2019	May-20	8	8- Months from fiscal year
Jul-2019	Jun-20	9	9- Months from fiscal year
Aug-2019	Jul-20	10	10- Months from fiscal year
Sep-2019	Aug-20	11	11- Months from fiscal year
Oct-2019	Sep-20	12	12- Months from fiscal year
Nov-2019	Oct-20	11	11 - Months are due to reach fiscal year Oct'20 from Start date
Dec-2019	Nov-20	10	10 Months are due to reach fiscal year Oct'20 from Start Date
Jan-2020	Dec-20	9	9 Months are due to reach fiscal year Oct'20 from Start Date
Feb-2020	Jan-21	8	8 Months are due to reach fiscal year Oct'20 from Start Date
Mar-2020	Feb-21	7	7 Months are due to reach fiscal year Oct'20 from Start Date
Apr-2020	Mar-21	6	6 Months are due to reach fiscal year Oct'20 from Start Date
May-2020	Apr-21	5	5 Months are due to reach fiscal year Oct'20 from Start Date
Jun-2020	May-21	4	4 Months are due to reach fiscal year Oct'20 from Start Date
Jul-2020	Jun-21	3	3 Months are due to reach fiscal year Oct'20 from Start Date
Aug-2020	Jul-21	2	2 Months are due to reach fiscal year Oct'20 from Start Date
Sep-2020	Aug-21	1	1 Months are due to reach fiscal year Oct'20 from Start Date
Oct-2020	Sep-21	0	Not in current Fiscal year
Nov-2020	Oct-21	0	Not in current Fiscal year
Dec-2020	Nov-21	0	Not in current Fiscal year

[v-frfei-msft]

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

 

For more details, please check the pbix as attached.

[Gururajv007]

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 date	End date	Column	Output needed	Status
1-Jul-18	1-Jun-19	0	0	Correct
1-Aug-18	1-Jul-19	0	0	Correct
1-Sep-18	1-Aug-19	0	0	Correct
1-Oct-18	1-Sep-19	0	0	Correct
1-Nov-18	1-Oct-19	12	1	Looks this reversed
1-Dec-18	1-Nov-19	11	2	Looks this reversed
1-Jan-19	1-Dec-19	10	3	Looks this reversed
1-Feb-19	1-Jan-20	9	4	Looks this reversed
1-Mar-19	1-Feb-20	8	5	Looks this reversed
1-Apr-19	1-Mar-20	7	6	Looks this reversed
1-May-19	1-Apr-20	6	7	Looks this reversed
1-Jun-19	1-May-20	5	8	Looks this reversed
1-Jul-19	1-Jun-20	4	9	Looks this reversed
1-Aug-19	1-Jul-20	3	10	Looks this reversed
1-Sep-19	1-Aug-20	2	11	Looks this reversed
1-Oct-19	1-Sep-20	1	12	Looks this reversed
1-Nov-19	1-Oct-20	11	11	Correct
1-Dec-19	1-Nov-20	10	10	Correct
1-Jan-20	1-Dec-20	9	9	Correct
1-Feb-20	1-Jan-21	8	8	Correct
1-Mar-20	1-Feb-21	7	7	Correct
1-Apr-20	1-Mar-21	6	6	Correct
1-May-20	1-Apr-21	5	5	Correct
1-Jun-20	1-May-21	4	4	Correct
1-Jul-20	1-Jun-21	3	3	Correct
1-Aug-20	1-Jul-21	2	2	Correct
1-Sep-20	1-Aug-21	1	1	Correct
1-Oct-20	1-Sep-21	0	0	Correct
1-Nov-20	1-Oct-21	0	0	Correct
1-Dec-20	1-Nov-21	0	0	Correct

[v-frfei-msft · 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"
    )

PBIX as attached.