Dynamic chart X axis - dates

[Metricbits]

Hello everyone,

I have this page with the balance sheet, which is filtered to one particular month (June 2021, in this case on the screenshot).

Also, I have a chart to the right of the balance sheet that I want to be showing the previous 12 months from the selected month (July 2020 to June 2021).

The way it currently works is - I disconnected the chart from the period selector that picks a period for the balance sheet and added another slicer that is only connected to the chart to pick the last 12 months manually.

I feel like there should be a better way to do this, so that chart can dynamically show the last 12 months from the period slicer that controls the balance sheet. Does anyone know if this is possible?

[HashamNiaz · Accepted solution]

Hi @Metricbits !

You can create a new measure using following DAX;

Assets Last (N) Month = 
    VAR _CurrentMonth = MAX('Calendar'[Date])
    VAR _LastNMonths = -12
    VAR _DatesPeriod = DATESINPERIOD('Calendar'[Date], _CurrentMonth, _LastNMonths, MONTH)
RETURN
    CALCULATE([Assets], _DatesPeriod)

This measure will return the Last (N) months (in this case Last 12 months) [Assets] value. Assuming you have Assets measure.

You can replace the [Assets] wiht your actual measure, it will bring Last 12 months value for that mesure.

You can have still have your Calendar / Date dimension table connected to your Fact table. From Date slicer it will pick up the selected date value & then go back 12 months.

To plot this on chart, you need to create a Dummy date column in your Fact table using below DAX;

Calendar = EOMONTH(FactTable[Date], 0)

[FactTable] will be the name of your Fact tabl & Date will be its date column. Now you use this field in your chart axis & place [Assets Last (N) Months] measure into values.

Regards,

Hasham

[Metricbits]

Worked like magic. Thank you so much!