Getting values in graph from last month of previous year till last month of selected year

[Anonymous]

Hi Team, I've following dataset in my report;

YearMonth	Quarter	Amount
201801	Q1	1412
201802	Q1	3534
201803	Q1	6542
201804	Q2	3254
201805	Q2	5342
201806	Q2	4653
201807	Q3	7653
201808	Q3	2324
201809	Q3	6443
201810	Q4	4324
201811	Q4	7655
201812	Q4	4545
201901	Q1	3423
201902	Q1	6544
201903	Q1	4232
201904	Q2	5436
201905	Q2	5342
201906	Q2	4653
201907	Q3	7653
201908	Q3	2324
201909	Q3	6443
201910	Q4	7451
201911	Q4	4324
201912	Q4	5343

For this i'm using Year filter to show monthly and quarterly graphs as follows:

1. Barchart for months and value as Amount

2. Barchart for quarters and value as Amount

I'm using Year as filter. I've data till 2021 so filter values are 2018,2019,2020 and 2021.

When I select 2019 it shows me 12 months from Jan to Dec for 2019. But I want to show current selected year and last month of previous year. Hence for 2019 it should be Dec 2018 to Dec 2019. Total 13 bars.

For 2020 it should be Dec 1019 to Dec 2020.. and so on.

Same I want to show for quarter. 

For 2019 it should be Q4 2018 to Q4 2019

and for 2020: Q4 2019 to Q4 2020 -  Total 5 Graphs.

Also note that i'm using direct query for this, hence countrows or earlier function won't work for me.

Let me know if this possible. Appreciate help.

Thank you!

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[mahoneypat]

One way to do this would be as follows

1. Make a disconnected Year table with the expression below, and use it in your Year slicer.

SlicerYears = DISTINCT('Date'[Year])

2. Make a measure with this expression (replacing with the table/column names from your Date table).

13 Months =
VAR maxdate =
    DATE ( SELECTEDVALUE ( SlicerYears[Year] ), 12, 31 )
VAR months13 =
    EOMONTH (
        maxdate,
        -13
    ) + 1
RETURN
    CALCULATE (
        [Total Sales],
        KEEPFILTERS (
            DATESBETWEEN (
                'Date'[Date],
                months13,
                maxdate
            )
        )
    )

3.  Make a bar chart with the YearMonth column from your Date table and this measure to get the result shown.

Regards,

Pat

[Anonymous]

Hi,

Thank you for the response. However could you please tell me which date[date] column you have used to get the datesbetween?

I dont have date column in my table.