locked
Time comparison problem with empty dates RRS feed

  • Question

  • Using a tabular data warehouse as a datasource, showing current years/current month sales, then trying to show previous month, previous year to date, same month prior year, etc. Our problem is dates that have no sales in the current year. For instance, 1/31 is a Sunday, so we have no sales. We build a DAX expression to show January of 2015 sales, 1/31 is not included in the calculation because it's empty in 2016.

    I've tried bringing our Calendar table in as a separate table and joining it to the MDX query that is the source for the PowerPivot table and charts, but haven't been able to make that work so far. Is there another answer? Thanks.

    Monday, February 1, 2016 10:16 PM

Answers

  • What calculations do you use to compute these measures?

    The built-in time intelligence functions in DAX don't depend on whether or not there are records in the fact table, because these are all filters on the Calendar table.

    For instance, CALCULATE([Sales],SAMEPERIODLASTYEAR(Calendar[Date])) will return the [Sales] for the same period in the previous year.

    • Proposed as answer by Charlie Liao Monday, February 22, 2016 8:35 AM
    • Marked as answer by Charlie Liao Tuesday, February 23, 2016 8:14 AM
    Tuesday, February 2, 2016 8:09 PM
    Answerer