March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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)))
Solved! Go to 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em Português
Well, doesn't want to work. Both Date tables are defined the same way and they are not related to anything.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
122 | |
98 | |
89 | |
73 | |
64 |
User | Count |
---|---|
138 | |
115 | |
115 | |
98 | |
98 |