Search code examples
powerpivot

PowerPivot YTD with Missing Data

Problem with computation of YTD if Data on a particular period is not existing

enter image description here

Using formula

YTDUnits =CALCULATE(SUM(Data[TYUnits]),DATESYTD(Data[FullDate]),ALL(Data[Month])))

Data came from a csv file, and am only using 1 table in the data model.

In this example, Month 2 should have a value of 6 on TransactionType c. Is there a way to fill this?

In the data model, no record exists for TransactionType c Month 2

enter image description here

Solution

  • I manually typed in your data sample, and built the data model:

    enter image description here

    Put Year-Month from the calendar table on the pivot, and added this DAX measure:

    YTDUnits = CALCULATE(SUM(Data[TYUnits]), DATESYTD('Calendar'[Date]))

    Result:

    enter image description here