Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic Reference line to indicate today's date

Hi,

I am trying to create a bar chart with a dynamic reference line, Can you please help me in acheving it.

 

In the x axis I will have values like -Q1. -Q2, -Q3, -Q4, Q1, Q2, Q3, Q4. This can be in months as well if drill down. Now I need a vertical reference line to indicate today's date (report logged in effective date). for example if I logged into the report today the line should be on Q1, If logged in on April then it should be on Q2. Can you please advise how this can be managed? I know I can achevie this via shapes but that's static, so I need something dynamic according to the user logged in date.

Thanks for your help

General Comment
Need Help
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi,  Anonymous 

     

    A vertical reference line is not available now. 

    To work around this requirement, you can try to use a combination chart.You can refer to this article:

    https://community.powerbi.com/t5/Desktop/Dynamic-date-vertical-reference-line-or-a-dot-in-a-line-chart/m-p/603550 

     

    And here is a sample I made:

    https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ERSq0fkx5U1Bor4-qEISrMwBbZ-Ee5Lj3aHzEf6pS1iVWA?e=feQnCQ 

     

    You need  to create a measure as below and add it to line and stacked column chart:

    Line2 =
VAR y =
    YEAR ( TODAY () )
VAR m =
    FORMAT ( TODAY (), "MMMM" )
VAR quar =
    FORMAT ( TODAY (), "q" )
VAR q = "Qtr " & quar
VAR d =
    DAY ( TODAY () )
RETURN
    IF (
        ISINSCOPE ( financials[Date].[Day] ),
        IF (
            d IN FILTERS ( 'financials'[Date].[Day] )
                && m IN FILTERS ( 'financials'[Date].[Month] )
                && q IN FILTERS ( 'financials'[Date].[Quarter] )
                && y IN FILTERS ( 'financials'[Date].[Year] ),
            1,
            0
        ),
        IF (
            ISINSCOPE ( financials[Date].[Month] ),
            IF (
                m IN FILTERS ( 'financials'[Date].[Month] )
                    && q IN FILTERS ( 'financials'[Date].[Quarter] )
                    && y IN FILTERS ( 'financials'[Date].[Year] ),
                1,
                0
            ),
            IF (
                ISINSCOPE ( financials[Date].[Quarter] ),
                IF (
                    q IN FILTERS ( 'financials'[Date].[Quarter] )
                        && y IN FILTERS ( 'financials'[Date].[Year] ),
                    1,
                    0
                ),
                IF (
                    ISINSCOPE ( financials[Date].[Year] ),
                    IF ( y IN FILTERS ( 'financials'[Date].[Year] ), 1, 0 )
                )
            )
        )
    )

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi,  Anonymous 

     

    A vertical reference line is not available now. 

    To work around this requirement, you can try to use a combination chart.You can refer to this article:

    https://community.powerbi.com/t5/Desktop/Dynamic-date-vertical-reference-line-or-a-dot-in-a-line-chart/m-p/603550 

     

    And here is a sample I made:

    https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ERSq0fkx5U1Bor4-qEISrMwBbZ-Ee5Lj3aHzEf6pS1iVWA?e=feQnCQ 

     

    You need  to create a measure as below and add it to line and stacked column chart:

    Line2 =
VAR y =
    YEAR ( TODAY () )
VAR m =
    FORMAT ( TODAY (), "MMMM" )
VAR quar =
    FORMAT ( TODAY (), "q" )
VAR q = "Qtr " & quar
VAR d =
    DAY ( TODAY () )
RETURN
    IF (
        ISINSCOPE ( financials[Date].[Day] ),
        IF (
            d IN FILTERS ( 'financials'[Date].[Day] )
                && m IN FILTERS ( 'financials'[Date].[Month] )
                && q IN FILTERS ( 'financials'[Date].[Quarter] )
                && y IN FILTERS ( 'financials'[Date].[Year] ),
            1,
            0
        ),
        IF (
            ISINSCOPE ( financials[Date].[Month] ),
            IF (
                m IN FILTERS ( 'financials'[Date].[Month] )
                    && q IN FILTERS ( 'financials'[Date].[Quarter] )
                    && y IN FILTERS ( 'financials'[Date].[Year] ),
                1,
                0
            ),
            IF (
                ISINSCOPE ( financials[Date].[Quarter] ),
                IF (
                    q IN FILTERS ( 'financials'[Date].[Quarter] )
                        && y IN FILTERS ( 'financials'[Date].[Year] ),
                    1,
                    0
                ),
                IF (
                    ISINSCOPE ( financials[Date].[Year] ),
                    IF ( y IN FILTERS ( 'financials'[Date].[Year] ), 1, 0 )
                )
            )
        )
    )

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.