locked
DAX / Measure - Date Parameters RRS feed

  • Question

  • New year and starting to get back into PowerPivot (now that I have it working).  I know I'm not using it PP or DAX to it's potential but I have ALOT of learning to do with DAX and measures.  I was generating a quick report via PP (can we find a shorter name for PowerPivot that doesn't sound like a bodily function?) and I came across a situation that I could not figure out how to handle, so here I am.  I have my sales data already configured for month / year and quarters all nice, but I had some specific report that had multi-year sales at different start /end dates, ie, instead of 2005 vs 2006, I needed April 2005 to March 2006 and then Apr. 2006 to March 2007 etc.  Just using slicers, if I pick the years or the months, it gives a calender year as soon as I select all 12 months - regardless of time frame. Is there a way to create an open measure that allows me to custom pick my start / end reporting dates and have it determine it's reporting history accordingly?

    Also, until I start using DAX/measures more, it's kind of hard to wrap my head around some of the new measures (no CUBE/SSAS experience).  Other than the descriptive terms - is there a source than can show examples of the new measures.  The date measures look interesting but just trying to grasp the syntax in put it to use in my tables isn't meshing well.  Mind you, like I said, I haven't spent a great deal of time playing around with DAX/Measures to get a good starting handle on it yet.
    Friday, January 8, 2010 7:23 PM

Answers

  • HI John,

    The most obvious problem with your formulas is that they don't include a measure. For example =sumx(FILTER('Sales','Sales'[CustomerNumber]="415247"),DATESBETWEEN('Sales'[InvoiceDate],"01-04-2008","31-03-2009")) is attempting to sum DATESBETWEEN('Sales'[InvoiceDate],"01-04-2008","31-03-2009"), which is meaningless. Moreover, in the second formula, (in addition to lacking a measure), you're attempting to filter the table twice (once with FILTER and then with CALCULATE).

    Would recommend = CALCULATE(SUM(<somemeasurecolumn>),'Sales'[CustomerNumber]="415247",DATESBETWEEN('Sales'[InvoiceDate],"01-04-2008","31-03-2009"), where <somemeasurecolumn> is a table column with a measure....or [Sum of <somemeasurecolumn>]('Sales'[CustomerNumber]="415247",DATESBETWEEN('Sales'[InvoiceDate],"01-04-2008","31-03-2009")). I've assumed that [CustomerNumber] is stored as text and that [InvoiceDate] is stored in the form dd-mm-yyyy.

    You could use SUMX as well, but it's only necessary here if <somemeasurecolumn> is a calculated measure column and not a column in the table.
    Friday, February 5, 2010 2:04 PM

All replies

  • If you could elaborate more on the detail requirement, in particular, which of the following scenarios describes your usage pattern? We may have different DAX solution depending on the requirement.

    1.       User selects start and end date
    2.       User selects the end date and wants the last 12 month of data, i.e. a 12-month "moving total". What do you mean "custom picked my start date" and "custom picked my end date"?
    3.       User selects start and end date on different table (after importing the Time table twice, as StartTime, EndTime).
    4.       Import the Time table twice, as StartTime, EndTime. User selects something on EndTime and wants the last year of data – a 12-month "moving total". or if they select something on StartTime (different table), then we do something similar by moving forward?

    Thanks,
    Lisa
    Monday, January 11, 2010 9:13 PM
  • In a perfect world, user (or I) would select start and end date as time frame - usually 12 months may fluctuate to quarters or other time frames. 

    Just wondering if there is a way to incorporate a measure to have open start / end dates .
    Tuesday, January 12, 2010 8:16 PM
  • One way is to import the time table twice, one as the start time, one as the end time. User can select member from the two tables using vertical and horizontal slicer. The following DAX calculated measure would then get the total sales between the starting and ending date point.

    TotalSales
    =SUM( 'FactAllSales'[SalesAmount])

    MovingTotal
    =[TotalSales](DATESBETWEEN(DimDate[FullDateAlternateKey], Min(DimDate1[FullDateAlternateKey]), Max(DimDate2[FullDateAlternateKey])),ALL(DimDate))

    Please let us know if this is what you are looking for.

    Thanks,
    Lisa

    Tuesday, January 12, 2010 10:33 PM
  • Another similar DAX measure would be

    MovingTotal

    =[TotalSales](DATESBETWEEN(DimDate[FullDateAlternateKey], FirstDate(DimDate1[FullDateAlternateKey]), LastDate(DimDate2[FullDateAlternateKey])),ALL(DimDate))

    Tuesday, January 12, 2010 10:38 PM
  • It looks like I may have to rework my raw data and table formats.  The dates I need Inv.Date are found in my sales data table.  As great as the potential is for PowerPivot as powerful tool for Excel, I find it's tie to Access nomenclature to be a little overpowering at times.

    To use this tool fully, I'm not sure if I should be studying Access programming theory or OLAP cubes. Back to the tutorials for me.
    Wednesday, January 13, 2010 3:07 AM
  • John,

    Another possibility. I get the impression that April to March represents a fiscal calendar and that you don't have a date dimension table with fiscal year, quarter and so on. If that's the case, you can create fiscal date calculated columns in your sales data table (based on Inv.Date) and use these columns as slicers in the PivotTable. For example, in the sales data table in the PowerPivot window, create these calculated fields:

    Fiscal Year ="FY"&YEAR([Inv.Date])+(MONTH([Inv.Date])>3)
    Fiscal Quarter ="FQ"&CEILING(MONTH(DATE(YEAR([Inv.Date]),MONTH([Inv.Date])-3,1)),3)/3

    From you first [post, I gather that you already have a month column in your sales data table.

    After you refresh the PivotTable to show the new fields in the field list, drag Fiscal Year (and Fiscal Quarter if you need it) to the slicer box. Now when you filter for FY2006, the PivotTable filters dates occurring between April 2005 and March 2006. Including FY2007 adds dates between April 2006 and March 2007 and so on.
    Wednesday, January 13, 2010 3:02 PM
  • Thank you Colin.  That solved part of the problem.  I do have many instances of fiscal year, but I still have some queries/reports that are open  - ie they could start /end at any time.  I give you full kudos on the coding - nice, simple, understandable and I've already put it to use, but for those situations with open start and end dates - that's where it will get cumbersome.
    Friday, January 15, 2010 2:32 PM
  • Just to be clear...do your start and end periods include granularity down to the day?
    Friday, January 15, 2010 10:32 PM
  • Yes - for accuracy purposes and do have the occasional data request for something like Nov 15 2008 to Nov. 14, 2009.
    Sunday, January 17, 2010 7:58 PM
  • There's no practical way to to filter down to the detail level in a summary type structure such as a PivotTable. If it were only Year & Month you had to deal with, then a calculated Year_Month field would solve the problem (you'd select beginning Year_Month to ending Year_month in the Year_Month slicer).

    I can think of two ways to solve the problem:

    1) Build an SQL aggregate query to pull in the sales data you need, summarized by day. Create start and end date parameters in your WHERE statement. Bring the data into an Excel Table. Use two cells that allows you to input the start and end date parameters. Then create a PivotTable from the Table source. The main problem with this approach is that you have to refresh the Table followed by the PivotTable - a two step process. With a bit of VBA code, you can get around this. However, if you're OK with VBA, then I prefer method 2, below.

    2) Build an SQL query and bring the data into a PivotTable. At this point, you can hard-code start and end dates into your SQL WHERE statement. Use two cells that allows you to input the start and end date parameters. Create a VBA routine where you feed the SQL query script into the PivotCache CommandText property. Adjust your original script so that it pulls start and end dates from your input cells into the SQL WHERE statement. To run the VBA code, you could add an "Update" button at the top of the PivotTable that executes the code after clicking the button.

    Both approaches assume that you don't need any of PowerPivot's additional functionality. And you'd have to create your fiscal year and quarter calculated columns in your query if you use method 2. 
    Monday, January 18, 2010 3:24 PM
  • Ok, I'm back ready to try to tackle this problem again.  The formula you propose looks like it could work however, I need to make it customer specific and ideally product family specific as well.  I know about the need to reference ALL() for date functions, but I don't have a defined date/time table so I'm not sure if it is still applicable.

    The formula for this 1st attempt checked out, however,it came out with #value, so somewhere there is a syntax issue:
    '=sumx(FILTER('Sales','Sales'[CustomerNumber]="415247"),DATESBETWEEN('Sales'[InvoiceDate],"01-04-2008","31-03-2009"))

    My second attempt follows your example a little closer but the same result:=CALCULATE(sumx(FILTER('Sales','Sales'[CustomerNumber]="415247"),DATESINPERIOD('Sales'[InvoiceDate],"01-04-2008",1,year)))

    I figure calculate would be the way to go as I can add more filters, in this case, product family (fruit/veggies eg.) or product specific (apples/oranges eg.).

    These are just used on the pivot table as measures.

    So, do I need to include All() as I am filtering specific dates and I don't need to reference all dates (as per syntax description) and - if I don't have a unique date/time table, would All(Sales) suffice?

    The important thing (for me) is that I'm still learning and I'm proud that I came up with a partially successful measure that resembled your own - which means I'm heading down the right path (I hope).  I just need some pointers.

    And thank you Colin for the fiscal qtr/year calcs.  They worked great in another endeavour.
    Thursday, February 4, 2010 9:08 PM
  • HI John,

    The most obvious problem with your formulas is that they don't include a measure. For example =sumx(FILTER('Sales','Sales'[CustomerNumber]="415247"),DATESBETWEEN('Sales'[InvoiceDate],"01-04-2008","31-03-2009")) is attempting to sum DATESBETWEEN('Sales'[InvoiceDate],"01-04-2008","31-03-2009"), which is meaningless. Moreover, in the second formula, (in addition to lacking a measure), you're attempting to filter the table twice (once with FILTER and then with CALCULATE).

    Would recommend = CALCULATE(SUM(<somemeasurecolumn>),'Sales'[CustomerNumber]="415247",DATESBETWEEN('Sales'[InvoiceDate],"01-04-2008","31-03-2009"), where <somemeasurecolumn> is a table column with a measure....or [Sum of <somemeasurecolumn>]('Sales'[CustomerNumber]="415247",DATESBETWEEN('Sales'[InvoiceDate],"01-04-2008","31-03-2009")). I've assumed that [CustomerNumber] is stored as text and that [InvoiceDate] is stored in the form dd-mm-yyyy.

    You could use SUMX as well, but it's only necessary here if <somemeasurecolumn> is a calculated measure column and not a column in the table.
    Friday, February 5, 2010 2:04 PM
  • Well, it looks like it's back to basics for me. I thought I knew the idea of measures, importance of filters, etc.  Obviously way off.  Thanks for the quick response!
    Friday, February 5, 2010 5:50 PM