Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi All
I am trying to show a rolling 12months of customers in a Matrix visual where the enddate is set by slicer and the startdate is always 12months before
this is what i have
NewBizCustomers =
VAR SelectedDate = LASTDATE(ALLSELECTED('Calendar'[Date]))
VAR SelectedYear = YEAR(SelectedDate)
VAR SelectedYearminus1 = YEAR(SelectedDate)-1
VAR SelectedMonth = MONTH(SelectedDate)
VAR SelectedDay = DAY(SelectedDate)
VAR CurrentYear = YEAR(TODAY())
VAR StartDate =
DATE(SelectedYearminus1,SelectedMonth,SelectedDay)
VAR EndDate =
DATE(SelectedYear , SelectedMonth, SelectedDay)
Var Result =
FILTER(
VALUES('List of ACTs'[CustomerName]),DATESBETWEEN('List of ACTs'[Customer Startdate], StartDate,EndDate)
)
RETURN
Result
however, this doesn’t filter the customer name.
I have looked through Forums but not having much luck - any help is very much appreciated.
EDIT:
here is screenshot of the data
i want it so that the startdate is always 12months behind the slicer
eg the able nz would not be on this list unless the date was 7/1/22
Solved! Go to Solution.
Found a way to do it by creating the following measure
CustomerInRolling12Months =
VAR SelectedDate = LASTDATE(ALLSELECTED('Calendar'[Date]))
VAR StartDate = EDATE(SelectedDate, -12) + 1
VAR EndDate = SelectedDate
RETURN
IF(
MIN('List of ACTs'[Customer Startdate]) >= StartDate &&
MIN('List of ACTs'[Customer Startdate]) <= EndDate,
1,
0
)
Then adding this to the visual filters and settings it to only show 1 as the result
Cheers all
Found a way to do it by creating the following measure
CustomerInRolling12Months =
VAR SelectedDate = LASTDATE(ALLSELECTED('Calendar'[Date]))
VAR StartDate = EDATE(SelectedDate, -12) + 1
VAR EndDate = SelectedDate
RETURN
IF(
MIN('List of ACTs'[Customer Startdate]) >= StartDate &&
MIN('List of ACTs'[Customer Startdate]) <= EndDate,
1,
0
)
Then adding this to the visual filters and settings it to only show 1 as the result
Cheers all
hi @Thomasshepherd2 ,
it seems you are writing a calculated table, which is not supposed to respond to visual behaviors (like slicer).
Please provide a workable sample data and your expected result from that. It is hard to figure out what you want to achieve from the description alone.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Made an edit to the post showing how i am wanting to show the data.
Let me know if it is still unclear.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
56 | |
56 | |
38 | |
29 |
User | Count |
---|---|
75 | |
62 | |
45 | |
40 | |
40 |