DAX formula to calculate YTD value starting with last calendar month

[Anonymous]

Hey everyone,

I'm trying to find a way to calculate the sum of a value for the year to date, starting with the last complete month.

For example, we are now in June so I would want a sum of this value for January 1st 2019 - May 31st 2019. In July I would want January 1st 2019 - June 30th 2019, etc.

I'm thinking it must be something like the following, but I'm struggling with getting it to work:

TOTALYTD(SUM(Table1[Column1]),DATEADD(DateDim[DateKey],-1,MONTH))

Any ideas?

Thanks!!

[Mariusz · Accepted solution]

Hi @Anonymous 

You can do something like below

Measure = 
VAR todaysDate = TODAY()
VAR PreviousMonthStart = DATE( YEAR( todaysDate ), MONTH ( todaysDate ), 1 )  
RETURN 
CALCULATE(
    [Sales],
    DATESYTD( 'Calendar'[Date] ),
    KEEPFILTERS( 'Calendar'[Date] < PreviousMonthStart )
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Wow, I wasn't even close. Thank you for your help!!

The only strange thing I am still seeing is that when I pull this into a visual to test it, I see values for every single date in 2019 up to December 31st. The dates in my fact table don't go past June. Any idea why it is doing this even when this DAX formula is specifying dates prior to the first of the month?