Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |