Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Urgent - Line Chart for 6 months (May-Oct)

Dear Community members,

 

I am trying to show the trend for the next 6 months (including the current month). For example, if the current month is May, the x-axis should show May-Oct, if the current month is June, the x-axis should show June-Nov and so on.

 

There can be a possibility, that one of the months might not have any data, still that month should be displayed on the x-axis. I tried multiple ways but no luck so far.

 

Help would be appreciated.

 

Thank You!

 

Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create a measure

    Measure =
VAR m =
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER (
            'date',
            'date'[Date]
                >= EOMONTH (
                    TODAY (),
                    -1
                ) + 1
                && DATEDIFF (
                    'date'[Date],
                    EOMONTH (
                        TODAY (),
                        -1
                    ) + 1,
                    MONTH
                ) <= 6
        )
    )
RETURN
    IF (
        MAX ( 'date'[Date] )
            >= EOMONTH (
                TODAY (),
                -1
            ) + 1
            && MAX ( 'date'[Date] )
                <= EOMONTH (
                    TODAY (),
                    5
                ),
        m + 0
    )

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

7 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous I'm sure it is possible using Time INtelligence function, can you share sample dataset and expected output, and will get back to you. There are may TI functions, Next Month, Next Quarter, Next Year etc and those will come handy to solve this.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      parry2k Thanks for your reply. Following is the actual VS expected output. The data is different in both, but in the 1st graph (actual), there is no data for August, but the line graph is not indicating 0 there. Below is some sample data as well.

       

       

       
       
      DataDateCount
      a29-06-2020 18:3014
      a23-09-2020 18:30-2.2
      a30-10-2020 18:30-2.2
      b21-07-2020 00:0011.5
      b20-10-2020 18:3011.9
      c20-10-2020 18:3030
      d09-11-2020 18:3012

       

      Thank You!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

    You can choose to "show items with no data"

     

    HTH,

    Michele

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous Thanks for the reply. But if I enable "Show items with no data" then it shows all the other months as well I want only 6 months to be displayed.

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

    Hi Anonymous 

    Create a measure

    Measure =
VAR m =
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER (
            'date',
            'date'[Date]
                >= EOMONTH (
                    TODAY (),
                    -1
                ) + 1
                && DATEDIFF (
                    'date'[Date],
                    EOMONTH (
                        TODAY (),
                        -1
                    ) + 1,
                    MONTH
                ) <= 6
        )
    )
RETURN
    IF (
        MAX ( 'date'[Date] )
            >= EOMONTH (
                TODAY (),
                -1
            ) + 1
            && MAX ( 'date'[Date] )
                <= EOMONTH (
                    TODAY (),
                    5
                ),
        m + 0
    )

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