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

7 Replies

  • 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

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