Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Running Total Showing Wrong Values

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Anonymous 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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.