Running Total Showing Wrong Values

[ptk10636]

I am using the below function to calculate the 12 month running total of items.

When all the periods are listed every thing works great. However if I filter and a period is missing a MYSTERIOUS "2" appears in the data.

CompRunning_alt =
VAR CompCount =
   CALCULATE (
      DISTINCTCOUNT( 'Extract (Header)'[issueNumber]),
      DATESINPERIOD (
         'Extract (Header)'[Month],
         MAX ('Extract (Header)'[Month] ),
         -12,
         MONTH
      )
   )
RETURN
   CompCount

All Months

With missi

[ptk10636 · Accepted solution]

I was able to resolve the issue by adding a seperate date table and not use the date in the main table. I do not know why this fixed the problem or why PBI was putting a MYSTERIOUS 2 when it should have been Null or Zero.

[Ashish_Mathur]

Hi,

I assume that Items is a measure which is calculated as = DISTINCTCOUNT( 'Extract (Header)'[issueNumber]).  If my understanding is correct, then how can you add distinctcount entries.  By its very nature, distinctcount cannot be additive.

[parry2k]

@ptk10636 very hard to tell without looking at the pbix file, can you share it, remove sensitive info before sharing.

Also as a best practice, do all time-based calculation using date dimension, means you should add a date dimension in your model to make it work.

here is a blog post that talks about how to add a date dimension to your model.