locked
DAX - TotalYTD not agregating past months if no value in the current month RRS feed

  • Question

  • Hi,

    I am working on a PowerPoint Solution and I have found an issue with my TotalYTD function.  At a high level of aggregation everything works great.  But when I drill down to a low level of granularity if there is no record in the current month the function will not aggregate past months.  For example  I have a value for July and a value for August but no value for September.  When I pull YTD for September I get a blank.   Any advise? Below is my DAX

    Actual YTD:=TOTALYTD(sum(Main_Tbl[Actual]),Main_Tbl[Fiscal Month],Main_Tbl[endytd],"6/1")

    Main_Tbl[Actual] is my measure, Main_Tbl[Fiscal Month] is my time dim and Main[endytd] is the first day of the period.  Again works great until I hit a low level of data.

    Thanks

    Thursday, September 20, 2012 10:21 PM

Answers

All replies

  • Try using the ISBLANK function.
    Friday, September 21, 2012 12:21 PM
  • Thank for the reply.  I'm not sure how ISBLANK would  be used in TotalYTD.  I can see using the ISBLANK if I wanted a blank but in this case I am looking for the total of the prior months.  I believe the issues is the function uses the date filter as a base to compile the measure and since there isn't data for that particular filter tuple in the pivot it doesn't aggregate for the past time periods.

    Friday, September 21, 2012 4:23 PM
  • I got it worked out.  The issue is I am using the time from my fact data not from a time dimension.  When I altered the problem was resolved.  Here the blog link that helped me out.  The blog talks about DatesYTD but this also applies to TotalYTD and TotalQTD and any other time function.

    http://www.powerpivotblog.nl/create-a-separate-related-time-table-for-time-intelligent-functions-in-powerpivot

    The new code was the following:

    =TOTALYTD(sum(Main_Tbl[Actual]),Time_Calc[Month],Main_Tbl[endytd],"6/1")

    Friday, September 21, 2012 5:45 PM