Creating a DAX calculation to create a Trend series Graph

[GThurairajah]

Hello I have the following problem

I have a pre-established Report with close to 50 different elements that are filtered at Page / Report level. The Report is designed to look at 1 days worth of information. Now I need to add a Trend series graph that operates independently of the page level filter preferably through a DAX calculation where it would create a graph by day for the past 28 days. That way I would have my existing report operate on a page daily filter level and a DAX calculation to return trendseries on multiple dates. 

_Please let me know if this is possible

Thanks

-Gaj

[TomMartens]

Hey,

you can use a measure like this

Measure = 
var selectedDate = CALCULATE(MAX('Calendar'[Date]))
var startDate = selectedDate - 28
var daterange = DATESBETWEEN('Calendar'[Date],startdate, selectedDate)
return
CALCULATE(
	SUM('FactWithDates'[Amount])
	,daterange
)

Assumptions about this measure

• there is a separate date table available (in my example this table is called "Calendar"
• there is an active relationship defined in the model on the one-side (Calendar) on the many-side (theFacttable)

The Measure works like this

• the selected date is stored in the variable "selectedDate"
• the startdate of the timeframe is stored in the variable startDate
• the daterange is stored in the variable "daterange" (this variable now contains a table with the dates of the daterange)
• Finally the measure is calculated expanding the exisiting filtercontext from the selected date to the daterange

Here is a little example of the outcome of this measure

Hope this gets you started

Regards

[GThurairajah]

Worked perfectly appreciate the help

[TomMartens · Accepted solution]

Hey,

if the answer solves your problem, mark the answer as solution, this will also help others.

Regards

If you find the solution, the explanation or something else special, do not hesitate to kudo the answer, in appreciation for the effort it takes to provide an answer that solves your issue 😉

[Anonymous]

Hi Tom,

I have a similar requirement but i need to show the trend for completed 3 Week/Months/ Quarters instead on Days. I have a Month/Quarter/ Week filter on the top ..is it possible to show the trend based on completed  periods instead on days??

Thanks & Regards,

Rajeev Bikkani

[TomMartens]

Hey Rajeev,

I will give this a closer look this evening (my time 🙂 ).

Can you please provide a link a pbix file, that contains sample data that enables us/me to answer your question more quickly.

Upload your file to OneDrive or Dropbox and than share a public accessible link.

Regards Tom

[GThurairajah]

Hello I have the following problem

I have a pre-established Report with close to 50 different elements that are filtered at Page / Report level. The Report is designed to look at 1 days worth of information. Now I need to add a Trend series graph that operates independently of the page level filter preferably through a DAX calculation where it would create a graph by day for the past 28 days. That way I would have my existing report operate on a page daily filter level and a DAX calculation to return trendseries on multiple dates. 

_Please let me know if this is possible

Thanks

-Gaj

[TomMartens]

Hmm,

to me this seems to be the exact the question I tried to answer here

https://community.powerbi.com/t5/Desktop/Creating-a-DAX-calculation-to-create-a-Trend-series-Graph/m...

besides the title of the post, if I'm wrong please correct me, if I'm right use the above mentionen link. For additional input, comments, and better solutions 😉

Regards