2012年4月26日 下午 01:12
Just wondering whether anyone can help me on the following:
So, I two tables in my dax query
- Calendar table that has all the dates(fiscal dates etc.)
- Line Item table that has all the item details.
On the line item table I have 2 columns which are subscription start and end dates. To capture the unique customers for a fiscal month I want to check and see what customers have valid subscriptions for the fiscal month.
The query I used was the following but this is not correct:
Within PowerPivot I have the fiscal month as a row label which splits it into the months but I can't seem to split the unique customer count in the same way. It seems to just return the full total.
Any help on this is greatly appreciated. Thanks you guys.
To do this in SQL, I've done the following but I want to do it now in PowerPivot so that I can create cool reports in PowerView.
SELECT COUNT(DISTINCT(customerID))as'Unique Customer Count',d.FiscalMonth'Fiscal Month'
FROM [dbo].[Order]o WITH (NOLOCK)
JOIN (SELECT OrderID
FROM [dbo].[LineItem]WITH (NOLOCK)
GROUP BY OrderID)Li
JOIN [dbo].[Calendar]d WITH (NOLOCK)ONd.dateBETWEENLi.StartDateAND Li.EndDate
2012年4月26日 下午 02:55
you cannot use DATESBETWEEN in this case
you have to use FILTER as you want to select only Lineitems, where the selected date is BETWEEN LineItemStart AND LineItemEnd
&& LASDATE(Dates[Date]) < Detals[SubscriptionEndDate])
(assuming that if you select e.g. a month you want to see valid subscriptions at month's end)
- www.pmOne.com -
2012年4月27日 上午 09:26
I'm trying to find what the number of customers that have had a subscription during the month. So if say there was a customer whose subscription was only for 2 weeks, say 01/01 - 01/15 they would not be counted in this list.
Any further help on this is greatly appreciated.
Thanks in advance,
2012年4月27日 上午 10:42
if this is the requirement then you have to change the second LASTDATE() to FIRSDATE()
this would return all Subscriptions where SubscriptionStartDate < LastDay of selected Period and SubscriptionEndDate > FirstDay of selected period
this should give you all rows that have been valid within the selected period and you do the DISTINCTCOUNT on top
- www.pmOne.com -
- 已標示為解答 Murtylad 2012年4月27日 上午 11:23
2012年4月27日 上午 11:23
Yes, so this seems to work for me now and it looks like it makes sense for my working example.
However, I have another problem now - where I have joined the detail table to the date table on the invoice date. So in effect I am losing a number of orders before the above logic is applied(as for each fiscal month I am bringing back all the details whose invoice date is within the fiscal month start and end date).
I have been thinking about using the USERELATEDTABLE function but am again confused because I can't really use this so that the datekey is spread across the start and end dates of the subscription.
I have been thinking of using a custom table where I bring back the customer ID, and fiscal month start date for every customer that exists during a fiscal month. That way I can join the Customers onto date table and arrnage it by fiscal date and get myself a true count.
I'm thinking though, this is a little messy. Would you have any suggestions on the same?
2012年4月27日 下午 12:46
there are 2 possible solutions to your problem:
1) remove the relationship between Details-table and Date-table
the relationship is then only established using DAX and the provided calculated measure
2) use ALL(Details) in your FILTER()-function to remove all current filters and apply only the new date filters
this will then not effect any other calculated measures
- www.pmOne.com -
2012年4月27日 下午 03:53
Ok, maybe I've been acting completely dumb, but I'm not able to get that working - If I do the following I'm bringing back the full totals regardless of fiscal period. But I still want the fiscal period breakdown( and that's given to be by the breakdown in the Date table so I kind need that relationship).
Details[Subscription Start Date] <= LASTDATE(Dates[Date])
&& FIRSTDATE(Dates[Date])<= Details[Subscription End Date]
Am I putting the All in the wrong place. I've put the ALL as a new filter but have returned the original results. Sorry about the back and forth. anything else to try is greatly appreciated. Thanks,M
2012年4月27日 下午 04:56
I have also tried using the ALLEXCEPT filter on the Details parameter and am having no luck :(