Zero-To-Date calculation

[gcrespo]

Hi everyone,

I'm trying to get the total number of rows from the beginning of time up to every month. Much like a YTD calculation but not resetting every year. So far, the best I've managed to get is a YTD calculation working:

YTD Implementations = TOTALYTD(CALCULATE (

    DISTINCTCOUNT ( 'modifications'[implementation_id] ),

    CONTAINSSTRING ( 'modifications'[description], "Desplegado en PRO" )

), 'FullDate'[Date])

What I need is the same but not resetting every year. Any ideas?

Regards,

Gabriel Crespo

[gcrespo]

Hi @parry2k ,

Thank you for your help. I already had a Date dimension in place. I wasn't able to find the right expression for the measure. Your message did the trick:

Total PRO Implementations =
CALCULATE (
    DISTINCTCOUNT ( 'modifications'[implementation_id] ),
    CONTAINSSTRING ( 'modifications'[description], "Desplegado en PRO" ),
    FILTER ( 
		ALL ( 'FullDate' ), 
		'FullDate'[Date] <= MAX ( 'FullDate'[Date] ) 
	)
)

Thank you and regards!

Gabriel Crespo 

[parry2k · Accepted solution]

@gcrespo 

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

After date dimension is added , measure will look something like this:

All Time RT = 
CALCULATE ( 
   DISTINCTCOUNT ( Table[Column] ),
   FILTER ( 
      ALL ( DateTable[Date] ),
      DateTable[Date] <= MAX ( DateTable[Date] )
   ),
   <<your containsstring expression here>>
)