Forum Discussion

anu2021's avatar
anu2021
Frequent Visitor
1 year ago
Solved

Help with Excel formula

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...
Need Help
  • mdaatifraza5556's avatar
    mdaatifraza5556
    1 year ago

    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.

danextian's avatar
danextian
Super User
1 year ago

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's avatar
    anu2021
    Frequent Visitor
    1 year ago

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