next 12 months on matrix

[Anonymous]

I have a matrix that shows text value based on a measure for each month. If slicer is selected, it drills down to the selected month. Say, if in slicer, 2023 feb is selected, it will show data for 2023 jan. 
Now i want to show in matrix, next 12 months data. i.e., 2023 jan, 2023 mar ... 2024 jan in column

#needhelp

[Anonymous]

Hi @Anonymous 

I tested it with simple data and here is my solution, hope it works for you.

1. Create a calendar table disconnected from the data table

CalendarTable = ADDCOLUMNS(
    CALENDAR(MIN('Table'[Date]), MAX('Table'[Date])),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "Day", DAY([Date]),
    "SortMonthNumber", MONTH([Date])
)

2. Create two slicers with the "Year" and "Month" fields of the CalendarTable.

3. Create the following measures:

InSelectedRange = 
VAR SelectedYear = SELECTEDVALUE('CalendarTable'[Year])
VAR SelectedMonth = SELECTEDVALUE('CalendarTable'[Month])
VAR SelectedYearMonth = SelectedYear * 100 + MONTH(DATEVALUE("1 " & SelectedMonth & " " & SelectedYear))
VAR EndYearMonth = SelectedYearMonth + 100
VAR CurrentYearMonth = YEAR(MAX('Table'[Date])) * 100 + MONTH(MAX('Table'[Date]))
RETURN
IF(CurrentYearMonth > SelectedYearMonth && CurrentYearMonth <= EndYearMonth, 1, 0)

CorrectValue = IF([InSelectedRange] = 1, [Value], BLANK())
// [Value] is a measure for text value

4. Create a Matrix visual with "Year" as the Rows, "Month" as the Columns, and the measure [CorrectValue] as the Values("Year" and "Month" filelds from fact data Table). And set the background color conditional format for the measure.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Jarvis, Thank you but i have another field for rows, and 2024 jan should come next to dec 2023

[Anonymous]

Hi @Anonymous 

Please provide sample data that completely covers your problem and describe the fields you used in your matrix visual as well as the measures. It would be nice to provide pbix files if possible. (Please remove privacy information)

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Not working. Also, i need to show in matrix, the monthname should be in column. value is a string

[123abc]

Plz follow bwlow community solution .. hopefully will help you to solve the issue:

How to create a matrix with12 months of rolling/trailing data in Power BI

Solved: How to create a matrix with12 months of rolling/tr... - Microsoft Fabric Community

I hope this helps!
If you found this answer helpful:

Mark it as the solution to help others find it faster.
Give it a kudo to show your appreciation!
Thank you for being an awesome community member!