I want to create a line graph in Power BI which shows last 12 months and the year before that.

[anirudh_bhatia]

What I want to achieve:-

I want to create a line graph in Power BI which shows last 12 months and the 12 months before that on 2 separate lines, and ordered dynamically by the selected month on the slicer. For instance, if I selected March - 2023 on my calendar slicer, the graph should show one line for the metric from April - March' 23, and the other line would show the same metric for April - Mar'22. 

Similarly if I select May -2023 on the calendar slicer, the graph should show data from June - May'23, and the second line should show data from June - May' 22. I am using the following DAX function:-

Income Prior 12 M = 

var enddate =  MAX('Calendar'[Date])

var startdate = EDate(enddate,-12)

RETURN

Calculate(sum(Income), AND ([EffectiveDate] > startdate, [EffectiveDate] <= enddate))

Income Prior 12-24 M = 

var oldEnddate =  MAX('Calendar'[Date])

var newEnddate = EDate(oldEnddate,-12)

var newStartdate = EDate(newEnddate,-12)

RETURN 

calculate(sum([Income]), AND ([EffectiveDate] > newStartdate, [EffectiveDate] <= newEnddate))

What's happening now:-

When I put these two measures on a graph, and use the effective Date (Month) field on the x-axis, the graph by default shows January - December, regardless of what month I select on the slicer. 

Note:- I cannot transpose my data as it is transactional data with inconsistent transaction types.

[anirudh_bhatia]

Thank you for your response @ADPowerBI1 !

So if you notice, I am not using the 'Effective Date' column from the date table, but from another table. The reason for this approach is that I have provided the user the option of choosing 1 single year-month combination in the slicer, which is the 'Date' column of the calendar table.

Since my purpose is to show the data for the last 12 months and previous 12-24 months, my DAX formula compares the 'Effective Date' column of my main data table with the date selected in the slicer. Please see below DAX formula to understand how this is happening :- 

var enddate =  MAX('Calendar'[Date])

var startdate = EDate(enddate,-12)

RETURN

Calculate(sum(Income), AND ([EffectiveDate] > startdate, [EffectiveDate] <= enddate))

So in my Line graph I am doing the following :- 

- Effective Month (Name of the month taken from the Effective Date column of my main data table) is placed on the x axis. 

- The Last 12 months, and last 12-24 months measures are placed on the Y-axis. 

You will see from the picture above that the x-axis is sorting months alphabetically. 

Why I have to do this:-

- If I pick the date from the calendar table, it will show only one month - because the slicer is a single select year-month combination. 

- If I pick the effective year and month date hierarchy, it will only show one single line going to the last 24 months of data.

What I need to do :- 

Sort this axis dynamically, so that when user clicks on April, the graph automatically shows May-April with the 2 lines overlapping like they are in the image. Similarly when user clicks on say February the graph updates for March-February with the 2 lines overlapping. 

Is it possible to create a dynamic column based on the slicer selection, or can this be done in some other way? Surely, there must be a solution to this?

The solution you provided doesnt work since it since creates a static column.

[ADPowerBI1]

You would need to create a column in your date table that shows the YEAR - WEEKNUM so that you can order it as such. for example, if you had a column

DATE
01/04/2023

Then the next column should be 
YEAR - WEEKNUM
2023 - 24

You would use this then to order the graph by (or you could use the year- weeknum as your X axis which would require slightly less configuration).

To do this create a calculated Column

year - weeknum = Concatenate(Concatenate(Year(Date), " - "),WEEKNUM(Date))

then put your new column in the TOOLTIP section of your visual.

Then click on your visual's menu:

And order by your new column.