cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Elegant formula to calculate past 14 days and compare same period previous month

The following formula calculates Total Sessions last 14 days (dynamic for the last date).



 

Total Sessions last 14 days = CALCULATE(SUM('Table1'[Total Sessions]),
	DATESBETWEEN('Table1'[Date],
		DATEADD(LASTDATE('Table1'[Date]),-13,DAY),
		LASTDATE('Table1'[Date])))

So, my assumption is that the current day is inclusive and thus including the past 13 days, in addition to the current, would yield me a measure of the last 14 days.

Measuring the Total Sessions with one-month lag I use the following formula

1 month back total sessions = CALCULATE(SUM('Table1'[Total Sessions]),
	DATESBETWEEN('Table1'[Date],
		DATEADD(LASTDATE('Table1'[Date]),-44,day),
		DATEADD(LASTDATE('Table'[Date]),-31,DAY)
))


If my understanding is correct of the formula above, it should return the Total Sessions over 14 days, lagged with 31 days from the current date.

Is there perhaps a more elegant solution, to calculating a period of 2 weeks dynamic from the last date in the dataset, compared to the same 2 weeks period but with a one-month lag?

Thanks

 

2 REPLIES 2
Highlighted
Frequent Visitor

Re: Elegant formula to calculate past 14 days and compare same period previous month

Any ideas :)?

Highlighted
Community Champion
Community Champion

Re: Elegant formula to calculate past 14 days and compare same period previous month

@dinovic

 

 

Measure 1onePrev =
VAR Month_1=DATEADD(LASTDATE(Table1[Date]);-1;MONTH)
RETURN
CALCULATE(SUM(Table1[Value]);DATESBETWEEN(Table1[Date];Month_1-13;Month_1))




Lima - Peru

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors