calculate a sum with a date filter today - 1 year

[Sebastian]

Hi,

i try to calculate a sum with values between today and today -1 year.

following syntax doesn't work:

Sum = Calculate(sum(table[value]);table[date]>= dateadd(today();-1;year))

Can't use today() function in time functions like dateadd or datesinperiod

hope someone could help.

[ZweiNeunSieben]

You can use something like this:

DATE(YEAR(TODAY())-1; MONTH(TODAY()); DAY(TODAY()))

[greggyb]

Time intelligence functions work on a date field in a date dimension. They do not work on scalar date values.

That being said, you can do simple arithmetic on dates:

RollingYearMeasure = 
CALCULATE(
	SUM( FactStupid[Amount] )
	;DimDate[Date] >= TODAY() - 365
)

[Sebastian]

Hi,

yes that works but it isn't a completly correct solution. The count of days switch between 364 to 365 days in addiction of a "normal" year and a leap year.

[greggyb]

That depends entirely on your calendar. If this is a concern for you, I'd set up a field in my date dimension that indicates today, say TodayFlag:

// Power Query
TodayFlag =
[Date] = DateTime.Date( DateTime.LocalNow() )

This will run as part of your queries every time the model is refreshed. So long as you refresh >=1 / day, it'll be up to date with your data.

In your report you can filter to TodayFlag = True - this will always filter to today's value. Then your measures can take advantage of SAMEPERIODLASTYEAR():

SamePeriodLastYear =
CALCULATE(
    <expression>
    ,SAMEPERIODLASTYEAR( DimDate[Date] )
)