Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a data table that has the following fields:
I have a measure that calculates the equivalent count last year:
I'm sorry those measures throw up errors for me. I've added an expected result column to my data that shows the percentage target that I expect to appear against each count. This is what I would use to add to the Count LastYr measure.
Thanks for the input but that's not the results I'm looking for.
I want my count table to show, for every date that there is a count, the CountLastYr * (1 + Target). The target percentage being the latest target set for that particular count type, location and event type.
Hi @mikemagill,
To create the measures as below. If it doesn't meet your requirement, kindly share your excepted result to me.
tar = var ma = CALCULATE(MAX('Date Table'[Date]),ALLEXCEPT('Date Table','Date Table'[LocationId],'Date Table'[EventNameId],'Date Table'[CountType])) return CALCULATE(MAX('Date Table'[Target]),FILTER('Date Table','Date Table'[Date]=ma))
Measure = VAR da = MAX ( 'Date Table'[Date] ) RETURN CALCULATE ( SUM ( 'Total Attendance'[Count] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] >= da - 364 && 'Table'[Date] <= da ) ) * 1+MAXX(ALLEXCEPT('Date Table','Date Table'[CountType],'Date Table'[EventNameId],'Date Table'[LocationId]),[tar])
Regards,
Frank
Here is a link to a spreadsheet containing two tables; CountData and Targets.
There should be sufficient data in there to demonstrate what I want to achieve.
Hi @mikemagill,
Please check the folloing steps as below. If it doesn't meet your requirement, kindly share your excepted result to me.
1. Create a date table and create relationships between tables.
Table = CALENDARAUTO()
2. Create a measure as below.
Measure = VAR da = MAX ( 'Date Table'[Date] ) VAR tar = MAX ( 'Date Table'[Target] ) RETURN CALCULATE ( SUM ( 'Total Attendance'[Count] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] >= da - 364 && 'Table'[Date] <= da ) ) * tar
Regards,
Frank
Hi @mikemagill,
Could you please share your sample data or pbix to me? You can upload your file to onedrive and share the link here.
Regards,
Frank
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
76 | |
74 | |
55 | |
45 |
User | Count |
---|---|
117 | |
106 | |
77 | |
66 | |
65 |