Forum Discussion
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. 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".
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))
7 Replies
Try like
Measure1 = CALCULATE(AVERAGE('Table'[Value]),Filter(Dim_Date,Dim_Date[Date] >= [StartDate] && Dim_Date[Date] <=[EndDate]))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 bEndDate = [StartDate] + 12The 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, hwoehlerWhen I open the file, it seems to me like working. What is the issue ?
