Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!