Calculating running totals in DAX

# Calculating running totals in DAX

• 26. dubna 2012 13:12

Hi All,

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:

=CALCULATE(

DISTINCTCOUNT(Details[Customer ID])

,DATESBETWEEN(

Dates[Date]

,Details[SubscriptionStartDate]

, Details[SubscriptionEndDate]

)

)

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.

Martin

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

,min(SubscriptionStartDate)

,max(SubscriptionEndDate)

FROM  [dbo].[LineItem]WITH (NOLOCK)

GROUP  BY  OrderID)Li

ON  Li.OrderID=o.OrderID

JOIN  [dbo].[Calendar]d  WITH (NOLOCK)ONd.dateBETWEENLi.StartDateAND  Li.EndDate

GROUP  BYd.FiscalMonth

### Všechny reakce

• 26. dubna 2012 14:55

hi,

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

CALCULATE(
DISTINCTCOUNT(Details[CustomerID]);
FILTER(
Details,
Details[SubscriptionStartDate]<LASTDATE(Dates[Date])
&& LASDATE(Dates[Date]) < Detals[SubscriptionEndDate])
)

(assuming that if you select e.g. a month you want to see valid subscriptions at month's end)

hth,
gerhard

- www.pmOne.com -

• Označen jako odpověď 27. dubna 2012 8:39
• Zrušeno označení jako odpověď 27. dubna 2012 9:24
•
• 27. dubna 2012 9:26

Not really,

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.

Martin

• 27. dubna 2012 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

hth,
gerhard

- www.pmOne.com -

• Označen jako odpověď 27. dubna 2012 11:23
•
• 27. dubna 2012 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?

• 27. dubna 2012 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

hth,
gerhard

- www.pmOne.com -

• 27. dubna 2012 15: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).

=CALCULATE(DISTINCTCOUNT(Details[Customer ID]),

FILTER(

ALL(Details),

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

• 27. dubna 2012 16:56

I have also tried using the ALLEXCEPT filter on the Details parameter and am having no luck :(

Thanks again,

M