How to show sales for trailing 12 months in table visual

[gvg]

Hi experts,

I am looking how to show Sales for trailing 12 months in a table visual.  Say, I need to show, for example, Sales from April in year X till May in year X-1. The year X and base month is selected in the slicer that uses a separate Date2 table that is not related to any other table. It is just used to pick up the base date. The Sales table is related to table Date. I have this measure defined, but it still returns all the dates, not just the ones, that are filtered by DATESINPERIOD. How should I modify my measure to achieve the described result?

Trailing 12 mo = CALCULATE([Amount],KEEPFILTERS(DATESINPERIOD('Date2'[Date],
                                                LASTDATE('Date2'[Date]),-12,MONTH)))

[MFelix]

Hi @gvg,

Make the relationship between the Datte table and the sales table inactive and then try to change your measure to somethin like this:

TREND_VALUES =
VAR Selected_Date =
    MAX ( Date2[Date] )
RETURN
    CALCULATE (
        [Amount];
        FILTER (
            ALL ( Sales[Date] );
            Sales[Date] >= EDATE ( Selected_Date; -12 )
                && Sales[Date] <= Selected_Date
        )
    )            

Regards,

MFelix

[gvg]

@MFelix

Well, doesn't want to work. Both Date tables are defined the same way and they are not related to anything.

[MFelix · Accepted solution]

Hi @gvg,

what is the date column you are using on the 12 rolling months table?

You need to use the date from the sales table not hte calendar table.

Regards,

MFelix

[gvg]

Hello @MFelix,

Yes, that works. But maybe there is another way as this is not very convenient to deactivate Date table in a lot of tables in a big model . Also needs rewriting all the measures to involve USERELATIONSHIP.

[MFelix]

Hi @gvg,

Give a try to this solution, it has a PBIX file that you can use to make it using the related DimDate table.

It's a different approach that requires more measures, but works pretty good.

Check it out and get back to me if you need any help.

Regards,

MFelix