Forum Discussion

GThurairajah's avatar
GThurairajah
Regular Visitor
9 years ago
Solved

Creating a DAX calculation to create a Trend series Graph

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 ...
  • TomMartens's avatar
    TomMartens
    9 years ago

    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 ;-)

TomMartens's avatar
TomMartens
Super User
9 years ago

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's avatar
GThurairajah
Regular Visitor
9 years ago

Worked perfectly appreciate the help

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        TomMartens
        Super User
        8 years ago

        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's avatar
    GThurairajah
    Regular Visitor
    9 years ago

     

    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