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

[Anonymous]

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!

[v-juanli-msft · Accepted solution]

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.

[mihart]

Hi @Anonymous 

You can choose to "show items with no data"

HTH,

Michele

[Anonymous]

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

[mihart]

Hi @Anonymous 

Here is a link to an article on quick measures.  

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-quick-measures

In that article you'll find instructions for creating 'last 6 months'

Take care,

Michele

[Anonymous]

Hi, @mihart  I need to show future 5 months (including the current month so, in total 6 months) I tried relative date filter, tried creating a new date table, but nothing seems to work.

Checked the URL you provided, and tried with time IntelliSense functions, but doesn't display what I require to show. It shows total of the last 6 months.

Thank you!

[parry2k]

@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]

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

Data	Date	Count
a	29-06-2020 18:30	14
a	23-09-2020 18:30	-2.2
a	30-10-2020 18:30	-2.2
b	21-07-2020 00:00	11.5
b	20-10-2020 18:30	11.9
c	20-10-2020 18:30	30
d	09-11-2020 18:30	12

Thank You!