Forum Discussion

ShyamS's avatar
ShyamS
Frequent Visitor
2 years ago
Solved

Need help in Dynamically selection of column value.

In my power bi report I want to create a dynamic header which will show Quarterly forecast based A column value.

I have a column called scenario having values ACTUAL, PLAN, DEC_FCST,MAR_FCST out of which MAR_FCST is the lastest quarter forecast. so in my report header It should show MAR_FCST. Now in next month (June) business will push JUNE FORECAST (JUN_FCST) anytime in next quarter time is not fixed when they will push but as soon as we will receive June forecast (jun_fcst) in scenario column out header should automatically update to jun_fcst and for next forecast which is September it should update to sep_fcst. So , please help to guide me how to achieve this in power bi, as I am new to the power bi.

Thank you in advance for your quick response and valuable time.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ShyamS ,

    I create a table as you mentioned.

    Then I create two calculated columns.

    Latest Forecast =
VAR _Current =
    MONTH ( TODAY () )
RETURN
    LOOKUPVALUE (
        'Table'[scenario ],
        'Table'[Date], DATE ( YEAR ( TODAY () ), _Current, 1 )
    )
    Next Month =
VAR _Current =
    MONTH ( TODAY () ) + 1
RETURN
    LOOKUPVALUE (
        'Table'[scenario ],
        'Table'[Date], DATE ( YEAR ( TODAY () ), _Current, 1 )
    )

    Then I use the Card visual and get what you want.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Yes I understand your question!

     

    In your case you can use this syntax it will work definitely:-

     

    Testmonthname = format(Today(),"mmm")

     

    If it works comment me.

     

    Thanks 😊 

     

     

    • ShyamS's avatar
      ShyamS
      Frequent Visitor
      2 years ago

      Thank you Dinesh for your reply.

      In this case every month header will change but in my case as soon as new entry will come into scenario column it will update accordingly that also quarterly basis not every month. 

      Let's say currently in scenario column latest one is MAR_FCST so it will display that till the new entry is not come into scenario column which will be JUN_FCST in next quarter. Then JUN_FCST will display till next quarter and then after that SEP_FCST will display and so on. 

      But my issue is how we will automatically detect weither any new entry is made into scenario column and based on that change the header.

      Please let me know if am not able to explain it properly. I will try to do it again.

      Thank you for your response,🙏

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ShyamS ,

    I create a table as you mentioned.

    Then I create two calculated columns.

    Latest Forecast =
VAR _Current =
    MONTH ( TODAY () )
RETURN
    LOOKUPVALUE (
        'Table'[scenario ],
        'Table'[Date], DATE ( YEAR ( TODAY () ), _Current, 1 )
    )
    Next Month =
VAR _Current =
    MONTH ( TODAY () ) + 1
RETURN
    LOOKUPVALUE (
        'Table'[scenario ],
        'Table'[Date], DATE ( YEAR ( TODAY () ), _Current, 1 )
    )

    Then I use the Card visual and get what you want.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.