Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
gvg
Post Prodigy
Post Prodigy

How to show sales for trailing 12 months in table visual

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)))

Pic1.jpg

 

 

 

 

1 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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix

 

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

 

Pic1.jpg

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.