Display date slicer in MMM-YYYY without the calculation being broken

[Anonymous]

I have a table in Power BI which has two date columns-

1. Data refresh month - it has the list of dates for every month and it's the date when monthly refresh of the data happens

2. Data week month - it has the value of 6 months for each refresh months. Selected month from Data refresh month + 5 previous months

Now I am using the data refresh months for calculating Current months sales & Previous months sales and displaying that in a line chart, with Data week month on X axis.

Also, there is a date slicer, and I need to show the just Month & year in the slicer, so I created a column using Data refresh month to extract month & year.

When I am making a selection from the slicer after introducing the MMM-YYYY (which is a text daya type), on line chart I could only see current sales, and the previous sales disappears.

I hope I was able to explain the situation.

What am I missing or doing wrong?

[Anonymous · Accepted solution]

Hi,

Thanks for Bibiano_Geraldo  and danextian 's concern about the problem, and i want to offer some more information for user to refer to.

hello @Anonymous ,  you need to create a calendar table to put it to the slicer without any relationship with data table, you can refer to the following sample.

Sample data 

Create a calaendar table(There are no relationship between tables.)

Calendar = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),TODAY() ),"Month-Year",FORMAT([Date],"MMMM-YYYY"))

Then create a measure.

MEASURE =
VAR a =
    EOMONTH ( MAX ( 'Calendar'[Date] ), -6 ) + 1
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Date] >= a
                && 'Table'[Date] <= MAX ( 'Calendar'[Date] )
        )
    )

Then put the "Month-Year" field of the calendar tablt to the slicer, and put the date column and the measure to the line visual.

Output

Best Regards!

Yolo Zhu

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

[Bibiano_Geraldo]

Hi @Anonymous ,

Please provide a sample data and share expected output and the issue you're facing

[danextian]

Hi @Anonymous 

It would be easier for us to figure out what you're trying to do if you provided a workable sample data (not an image), your expected result from that and the reasoning behind.