Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
Dear community,
I'm creating a report where a user can filter the data by year and month. Within this report, I would like to include a visual that displays trends, including data from the month/year selected and the 12 months before that. I have created measures that calculate the first and last date of the trend figure;
Hello:
Some good news and some bad news. You can't use measures as slicers/filters.
But you can create a calculation for say sales, e.g.
Sales = SUM(SalesTable[Revenue])
if you have data for 13 months or more, to get your trend you can use relative Date Slicer use Date field from your Date Table. Here's what is looks like and you can apply it to whichever visuals you like. Have a Date Table connected to your fact table makes life so much easier and yo can use all the built in time intelligence measures Power BI offers.
To Make a Date Table (Basic). Let's say for 2021 thru 2022
Go to Modeling.
Choose New Table. We can name it Dates.
Dates = CALENDAR(DATE(2021,1,1), DATE(2022,12,31))
You can add columns for Year & Month and Year-Month , etc
YEAR = YEAR(Dates[Date])
Month No. = MONTH(Dates[Date])
Year & Month No. = FORMAT(Dates[Date], "YYYYMM")
Then under Table tools at the top slect Mark as Date Table. Choose Date field and click OK.
Now you can connect this Date Table to a table of facts that usually have a date and some tye of transaction that goes with it, like sales, a visit, any factual type of data.
I could go on but want to see if this is helpful first. See relative slicer below using Date field from new Date Table. You can set it to 1st of the month, etc. Lot's of options.
Hi @Anonymous
In general you can follow this rule
New Measure =
VAR Fdate =
EDATE (
DATE ( SELECTEDVALUE ( 'Date'[DateYear], 0 ), SELECTEDVALUE ( 'Date'[DateMonth], 0 ), 1 ),
-12
)
VAR Ldate =
DATE ( SELECTEDVALUE ( 'Date'[DateYear], 0 ), SELECTEDVALUE ( 'Date'[DateMonth], 0 ) + 1, 1 ) - 1
VAR Result =
CALCULATE (
[Old Measure],
REMOVEFILTERS ( 'Date' ),
'Date'[Date] >= Fdate,
'Date'[Date] <= Ldate
)
RETURN
Result
@Anonymous , if you need only rolling 13 month data
Rolling 13 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-13,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))
Rolling 13 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-13,MONTH))
But if you select 1 month and want to have a trend for 13 months then you need an independent date table
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Hi Amit,
Thank you so much for the reply - I have tried it and watched your video and is very informative, and the solution works. 🙂
I however still have a problem, the L13M trend visual is part of a report where we're going deep dive into one month. I therefore want the user to select a month in a Slicer, see some data regarding only that month, and then see the L13M trend visual as well (with the last month being the selected month). Is there any way to do this, without having the user select the month & year in a slicer for the monthly overview, and having to select it again in a seperate slicer for the L13M visual? Can I do this with a single slicer?
Thanks again for your help!
Best,
Eva
User | Count |
---|---|
15 | |
9 | |
8 | |
6 | |
5 |
User | Count |
---|---|
30 | |
19 | |
12 | |
7 | |
5 |