Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. 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
User | Count |
---|---|
121 | |
76 | |
63 | |
51 | |
50 |
User | Count |
---|---|
175 | |
125 | |
60 | |
60 | |
58 |