Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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 |
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |