locked
Calculating running totals in DAX RRS feed

  • Question

  • 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

    Thursday, April 26, 2012 1:12 PM

Answers

  • 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 -

    • Marked as answer by Murtylad Friday, April 27, 2012 11:23 AM
    Friday, April 27, 2012 10:42 AM
    Answerer

All replies

  • 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 -

    • Marked as answer by Murtylad Friday, April 27, 2012 8:39 AM
    • Unmarked as answer by Murtylad Friday, April 27, 2012 9:24 AM
    Thursday, April 26, 2012 2:55 PM
    Answerer
  • 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.

    Thanks in advance,

    Martin

    Friday, April 27, 2012 9:26 AM
  • 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 -

    • Marked as answer by Murtylad Friday, April 27, 2012 11:23 AM
    Friday, April 27, 2012 10:42 AM
    Answerer
  • 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?

    Friday, April 27, 2012 11:23 AM
  • 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 -

    Friday, April 27, 2012 12:46 PM
    Answerer
  • 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

    Friday, April 27, 2012 3:53 PM
  • I have also tried using the ALLEXCEPT filter on the Details parameter and am having no luck :(

    Thanks again,

    M

    Friday, April 27, 2012 4:56 PM