Help with Excel formula

[anu2021]

I am trying to do a date mapping in the following data basis the the value present fields...  Value > 0 , the first value month will start month and the last value >0 should be the end month.. Sample data i have given... Could you please help

Identity	1-Jan-25	1-Feb-25	1-Mar-25	1-Apr-25	1-May-25	1-Jun-25	1-Jul-25	1-Aug-25	1-Sep-25	1-Oct-25	1-Nov-25	1-Dec-25	1-Jan-26	1-Feb-26	Start Period	End period
Item1	0	0	1	2	2	2	2	2	0	0	0	0	0	0	25-Mar	25-Aug
Item2	0	1	2	2	2	2	2	0	0	0	0	0	0	0	25-Feb	25-Jul
Item3	0	1	2	2	2	2	2	0	0	0	0	0	0	0	25-Feb	25-Jul
Item4	0	0	0	0	0	1	2	2	2	2	2	0	0	0	25-Jun	25-Nov
Item5	0	0	0	0	0	1	2	2	2	2	2	1	1	0	25-Jun	26-Jan

[mdaatifraza5556 · Accepted solution]

Hi @anu2021 

Can you please try the below steps to get your requirement ?

1. Use below formula to get Start Period

=TEXT(INDEX($B$1:$O$1, MATCH(TRUE, INDEX(B2:O2>0, 0), 0)), "yy-mmm")

2. Use below formula to get End Period
=TEXT(INDEX($B$1:$O$1, MATCH(1, INDEX((B2:O2>0)*(COLUMN(B2:O2)=MAX(IF(B2:O2>0, COLUMN(B2:O2)))), 0), 0)), "yy-mmm")

If this answers your questions, kindly accept it as a solution and give kudos.

I have also attached the excel file.

[anu2021]

This helps and solves the problem for 2 lakh+ records... Thank you 🙂

[danextian]

Hi @anu2021 

Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. The sample data must actually represent your raw data and not simply the desired output. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud. Also, your image is very hard to read

[anu2021]

hi, I am trying to attach the file.. however i am not finding an option to attach.. so updated it with the table view intead of image..

[jaineshp]

Hey @anu2021,

Could you please attach the excel file instead of image?

Image won't help understand the scenario.

Best regards,
Jainesh Poojara / Power BI Developer

[anu2021]

i am unable to attach the file.. so tried pasting it in table view instead of Image.. 😞