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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors