This topic contains 2 replies, has 2 voices, and was last updated by 
-
Posts
-
How would I go about calculating the net total of records added or cancelled in a time period? Let’s say each record contains nothing more than a key, date created, and date cancelled. The first two fields are never NULL, but date cancelled can have a date or be NULL. How would I create a measure that would tell me the net count of records for a variable date range? I could do it for a defined period, but I can’t figure out how to make it flexible.
Thanks for any help you can offer.
Hello volfied,
Good to have you back again in the powerpivotpro forums!
Just tossing out some ideas: you would need a calendar table in addition to your data table. I can picture two relationships: the first is an active relationship between data[date created] and calendar[date] and an inactive relationship between data[date cancelled] and calendar[date].
Measures would look something like:
Total Created := COUNTROWS ( data )
Total Cancelled := CALCULATE ( COUNTROWS ( data ), USERELATIONSHIP ( data[date cancelled], calendar[date] ) )I would hide all columns in the data table from client tools (leaving only measures to show in the field list under the data table).
I would use fields from the calendar table in pivot row, column, filter and slicer drop zones and, of course, the measures in the values zone. Then I would test and tweak until satisfied.
Rules for calendar table:
1) Must cover the date range of the model (here, the date range of the data table)
2) Dates must be contiguous (no gaps)
3) Must have a column of type date
4) Time component of the date column must be empty
5) Mark as Date Table in Power Pivot windowDo you have some sample data we could use to test this?
Tom
Sorry for not replying sooner. I got swamped with new stuff at work and this slipped my mind. I do have a Calendar Table that I use. I’ve been going about multiple dates by creating multiple instances of it, but it sounds like USERELATIONSHIP() would be the better approach. I’ll read up on that and let you know if I’m still stuck.
Thank you!
The forum ‘Power Pivot’ is closed to new topics and replies.