Paroxysm86
New Member
Hi all,
I'm currently trying to create a reporting process, and I know exactly what I want to do, but I have no idea how to do it.
Essentially, we have a list of appointment data which needs reporting on, which can broadly be split in two. Some columns need to be reported on in the month the appointment was booked, and some in the month the appointment was sat. Anything that's booked in the month its sat will get picked up in the current iteration because the managed fields picks up both separately, dependant on which field they are looking at.
I've created some helper columns in the spreadsheets to be reported on, which pull the relevant reporting month for both sections of the data and places them in a column to the right.
What I want to do, is to be able to easily split these out for reporting purposes. The long way round is to create managed fields for each month, and then import these individually into pivot-tables, but this is obviously quite manual. I had the brainwave of putting a drop-down menu on the page with the various months, getting Powerpivot to pull this into the data and then seeing if it could reference, but it didn't work.
An example of the sort of thing is follows. This is a simpler DAX formula I've crafted, intended to count new clients and return how many appointments were booked for [month].
Essentially, I need to know how to get DAX to refer to a text string. That's what I'm trying to do with this piece of code - [Month] returns the month currently selected on the sheet, and I'd like this part of the code to cross reference what's in [booking month] with the managed field [Month]. If this isn't possible (or even if it is) is there a better way?
The reason I'm doing it in DAX rather than simply mocking something up in XL is that there's several calculated fields, and several teams - so it makes sense to make Powerpivot do the heavy lifting
Many thanks!
I'm currently trying to create a reporting process, and I know exactly what I want to do, but I have no idea how to do it.
Essentially, we have a list of appointment data which needs reporting on, which can broadly be split in two. Some columns need to be reported on in the month the appointment was booked, and some in the month the appointment was sat. Anything that's booked in the month its sat will get picked up in the current iteration because the managed fields picks up both separately, dependant on which field they are looking at.
I've created some helper columns in the spreadsheets to be reported on, which pull the relevant reporting month for both sections of the data and places them in a column to the right.
What I want to do, is to be able to easily split these out for reporting purposes. The long way round is to create managed fields for each month, and then import these individually into pivot-tables, but this is obviously quite manual. I had the brainwave of putting a drop-down menu on the page with the various months, getting Powerpivot to pull this into the data and then seeing if it could reference, but it didn't work.
An example of the sort of thing is follows. This is a simpler DAX formula I've crafted, intended to count new clients and return how many appointments were booked for [month].
Code:
New Clients:=Calculate(COUNTROWS('Data'),'Data'[Type] ="New",'Data'[Booking Month]=[Month])Essentially, I need to know how to get DAX to refer to a text string. That's what I'm trying to do with this piece of code - [Month] returns the month currently selected on the sheet, and I'd like this part of the code to cross reference what's in [booking month] with the managed field [Month]. If this isn't possible (or even if it is) is there a better way?
The reason I'm doing it in DAX rather than simply mocking something up in XL is that there's several calculated fields, and several teams - so it makes sense to make Powerpivot do the heavy lifting
Many thanks!
Last edited: