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

[hwoehler]

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. This only works if I enter the dates directly in the measure with "Date(...)". Measure = CALCULATE (MAX (Table [Value]); DATESBETWEEN (Table [Date]; Date (2015; 08; 01); Date (2015; 08; 31)))) Since the dates change dynamically, I would like to have a measure like this: Measure = CALCULATE (AVERAGE (Table [Value]); Filter (Dim_Date; DATESBETWEEN (Table [Date]; [StartDate]; [EndDate]))). However, for this measure I get an error "a table of multiple values was supplied where a single value was expected" (see left chart in the picture).  Can someone help me? Thanks! Regards, hwoehler

PBI File link: https://we.tl/t-PtnHkxqHIE

Note: I created the date measures "EndDate" and "StartDate" in the PBI file with "Date (..)". This is just for simplification. Usually these two measures are dynamic. My only concern here is that I can use the measures in "DatesBetween".

[amitchandak]

Try like

Measure1 = CALCULATE(AVERAGE('Table'[Value]),Filter(Dim_Date,Dim_Date[Date] >= [StartDate] && Dim_Date[Date]  <=[EndDate]))

[hwoehler]

Hi @amitchandak ,
thanks for the solution! The solution fits the Power BI file I have provided. However, I don't get a correct solution for the following situation: My measures StartDate/EndDate are:

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

EndDate = [StartDate] + 12 

The date range thus refers to another column ("Win"), where the respective date is used as the start date for the highest value. If I then use the measure "StartDate" in the same formula for the line diagram (

Measure1 = CALCULATE(AVERAGE('Table'[Value]);Filter(Dim_Date;Dim_Date[Date] >= [StartDate] && Dim_Date[Date]<=[EndDate])) ) 

, the result is wrong. The start date is not recognized. How can this be solved?

Here again the updated file: https://we.tl/t-YNzqYIF25S
Thanks, hwoehler

[Angelos_ms]

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]

Hi @Angelos_ms ,
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.

[amitchandak]

When I open the file, it seems to me like working. What is the issue ?

[hwoehler]

Hi @amitchandak , unfortunately not. The date range is incorrect. (At the moment, all values ​​are shown in the left graph). I would like to have only a part of it, in the screenshot it would be 7/23/2009 to 8/4/2009 (start and end date).

Best regards,hwoehler

[amitchandak · Accepted solution]

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