locked
PowerPivot Dates with no Data RRS feed

  • Question

  • I've got a classic "fact" table with Membership data linked to a "date" dimension table.  The fact table stores membership data at each month end date.  My Members Added measure works as expected, but Members Added YTD repeats for every date in the Dim_Date table.  I wasn't expecting that.  I only want to show the dates for which there is data.

    Members Added:  =CALCULATE(SUM(Fact_MemberWalk[MemberCount]),Fact_MemberWalk[TranType]="New Member")

    Members Added YTD: CALCULATE([Members Added],DATESYTD(Dim_Date[DateKey]))

    Picture of Problem:


    Kirk P.

    Monday, April 27, 2015 3:21 PM

Answers

  • Hi Kirk,

    You could try something similar to the following:

    Members Added YTD:=
    CALCULATE(
      [Members Added], 
      DATESYTD(
        FILTER(
          VALUES('Dim_Date'[DateKey]), 
          [Members Added]
        )
      )
    )

    We're using the FILTER table expression to only return a list of dates that have a non-blank value for the [Members Added] Calculated Field. This list of dates is then passed to the DATESYTD function which is then able to determine the last date within the year with some data.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Monday, May 4, 2015 8:39 AM

All replies

  • Hi Kirk,

    According to your description, this issue is more related to Power Pivot. The Power Pivot forum is a better place for Power Pivot issue, we will move it there for you.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 28, 2015 1:31 AM
  • Kirk, are you still trying to get this figured out?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, May 1, 2015 8:46 PM
  • Hi Kirk,

    You could try something similar to the following:

    Members Added YTD:=
    CALCULATE(
      [Members Added], 
      DATESYTD(
        FILTER(
          VALUES('Dim_Date'[DateKey]), 
          [Members Added]
        )
      )
    )

    We're using the FILTER table expression to only return a list of dates that have a non-blank value for the [Members Added] Calculated Field. This list of dates is then passed to the DATESYTD function which is then able to determine the last date within the year with some data.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Monday, May 4, 2015 8:39 AM
  • Hi Kirk,

    Did you get a chance to try the above approach or solve this another way?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Saturday, October 17, 2015 2:08 PM