topic Re: YTD Sales - exlude data with future date in DAX Commands and Tips

YTD Sales - exlude data with future date

JohnnyC — Thu, 29 Aug 2024 12:58:40 GMT

Hi All,

I'm trying to get YTD value of a column (service fees). I have calender column and the YTD sum looks to work fine but it is summing all values.

My issue is that there is data in there with renewal date in the future. Eg today is 29th August i just want to see the sum of service fees from 1st Jan until today ( i want to exclude any with future renewal date if that makes sense)

Does anyone know how i can do this?

thanks,

Johnny

Re: YTD Sales - exlude data with future date

Rupak_bi — Thu, 29 Aug 2024 13:36:36 GMT

Hi,

The DAX is as below based on what you have asked. I am assuming there is no groupby or other filters to take care.

YTD =

calculate(

sum(table[values]),All(Table),table[date]>=date(2024,1,1)&&Table[date]<=today()

)

Re: YTD Sales - exlude data with future date

xifeng_L — Thu, 29 Aug 2024 13:48:05 GMT

You can try the TOTALYTD or DATESYTD functions. Eg:

MeasureName = CALCULATE(SUM('Table'[Values]),DATESYTD('Calendar'[Date]=TODAY()))

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: YTD Sales - exlude data with future date

JohnnyC — Thu, 29 Aug 2024 14:00:14 GMT

that's it - I've been all morning trying to get that (i'm not an IT guy but learning). thanks a mil