13 Month Table with Slicer Control?

[mannequinxox]

Hi there,

I am very new to Powerbi and I am creating an incident report that exists in excel in powerbi 

so I have a slicer on the first page where you can choose a reporting month and things will move to that - this is fine for tables for the month but I have a table that needs to show 13 months including the month on the slicer. So if the slicer is June 2020 it would need to show from June 2020 - June 2019. Does that make sense?

An issue I am having is that I created the slicer for its Month Year (June 2020) - I did this via a calculated column and would like it to stay as text. I am having issues figuring out the best way to approach this - I am very new to DAX and this report is updated monthly, so I can't use any functions that use today's date.

Any help would be greatly appreciated - TIA!

[qqqqqwwwweeerrr · Accepted solution]

Hi @mannequinxox 

This video might help you to solve your problem: (Note this is not the exact solution you might need to tweak as per you data model): https://youtu.be/uQPXYW79m3A

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards

[Anonymous · Accepted solution]

Hi,qqqqqwwwweeerrr ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@mannequinxox .I am glad to help you.
According to your description, you want to implement a date slicer (when month is selected) with text type to filter out the data from the selected month of the current year to the current month of one year ago, for example, select January-2024,filter out the data from 2023/1/1 - 2024/1/31.
If I understand correctly, I recommend you to use measure to customize the filter range.
Here are the test results.

It should be noted that since the slicer's filter field type is text type, power BI can not directly through the text type of the field to achieve continuous filtering. So my approach is to let the slicer to provide filtering date values, the text value into the correct date range, and ultimately through the custom date parameter range of the data tagged to display the last year's data (including the current selection of the month)
Here's my test data :

I created a separate slicer form for the slicer field

M_result2 = 
VAR MonthYear = SELECTEDVALUE('slicer_2'[DateMonth])
VAR Month = SWITCH(
    TRUE(),
    CONTAINSSTRING(MonthYear, "January"), 1,
    CONTAINSSTRING(MonthYear, "February"), 2,
    CONTAINSSTRING(MonthYear, "March"), 3,
    CONTAINSSTRING(MonthYear, "April"), 4,
    CONTAINSSTRING(MonthYear, "May"), 5,
    CONTAINSSTRING(MonthYear, "June"), 6,
    CONTAINSSTRING(MonthYear, "July"), 7,
    CONTAINSSTRING(MonthYear, "August"), 8,
    CONTAINSSTRING(MonthYear, "September"), 9,
    CONTAINSSTRING(MonthYear, "October"), 10,
    CONTAINSSTRING(MonthYear, "November"), 11,
    CONTAINSSTRING(MonthYear, "December"), 12
)
VAR Year = VALUE(RIGHT(MonthYear, 4))
VAR _selectDate= DATE(Year, Month, 1)
VAR _tableDate=MAX('Table'[Date])
VAR _startDate=EOMONTH(_selectDate,-13)+1
VAR _endDate=EOMONTH(_selectDate,0)
// _startDate:Returns the beginning of the month one year before the selected date
// _endDate:Returns the last day of the selected date
RETURN
IF(_tableDate>=_startDate && _tableDate<=_endDate,1,0)

The data is eventually filtered by M_result2:

I have also found other issues that have similar needs and have been resolved, so I hope this helps.
URL:
Solved: How to - Make a dynamic continous X axis based on ... - Microsoft Fabric Community
 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[qqqqqwwwweeerrr · Accepted solution]

Hi @mannequinxox 

This video might help you to solve your problem: (Note this is not the exact solution you might need to tweak as per you data model): https://youtu.be/uQPXYW79m3A

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards