Forum Discussion

hwoehler's avatar
hwoehler
Helper I
6 years ago
Solved

Show Values between two dates (start/end date as measures)

Hello everybody, I would like to create a line diagram with the "Value" values ​​between two dates (StartDate and EndDate). You can see the desired result on the right chart in the picture below. Th...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Try like

    Measure1 = 
var _min = minx(SUMMARIZE(ALLSELECTED('Table'),"_a",[StartDate]),[_a])
var _max =minx(SUMMARIZE(ALLSELECTED('Table'),"_b",[EndDate]),[_b])
return
 CALCULATE(AVERAGE('Table'[Value]),Filter(Dim_Date,Dim_Date[Date] >= _min && Dim_Date[Date]<=_max))
//CALCULATE(COUNTROWS('Table'),Filter(Dim_Date,Dim_Date[Date] >= _min && Dim_Date[Date]<=_max))

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

If I am to deduce this correctly your formula for [StartDate] will only ever give you the exact same date as on the X-Axis

 

Can you explain a little further with a table of results what you expect, as you are trying to find the FirstDate which matches the same wins but you are currenlty in the context of a single date on the X Axis, hence you only ever get the current date, and your formula works just fine in the latest Power BI Desktop


StartDate = var a = max ('Table' [Win]) var b = CALCULATE (FIRSTDATE (Dim_Date [Date]); Filter ('Table'; 'Table' [Win] = a)) return b

 

PS. You can try to debug this by using tables instead of graphs, and see the result of each variable like a, b, startdate.

 

Hope this helps



hwoehler's avatar
hwoehler
Helper I
6 years ago

Hi Anonymous ,
my description was perhaps somewhat misleading.
In a nutshell: The values ​​("Values") for a certain date range should be displayed, whether it is a table or a line diagram. The date range is given by the start and end date, but the dates change depending on the selection of the company. The start and end dates are calculated using measures. The start date depends on the "Win" column (and on the company, as mentioned). In the example it is the date "7/23/2009" for "Win" = 11 for the Company Biotest Vz. The end date is then [StartDate] + 12 (12 is added to the start date). The right graph in the screenshots shows the desired result. Here, however, I worked with specified dates for the measures StartDate and EndDate (With Date (02,03,2009) as an example). Strangely, this does not work if Date (..) does not use, instead use the date 7/23/2009 (based on the "Win" column). The formula "StartDate" works correctly, so the date is correct, only the graph does not work. I just don't understand why it works with "Date (...)" (see Measure2; Measure2 = CALCULATE (MAX ('Table' [Value]); DATESBETWEEN ('Table' [Date]; Date (2015; 08; 01); Date (2015; 08; 31)))) ) and not otherwise.