cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hwoehler Helper I
Helper I

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".
Unbenannt.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

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

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

 

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


View solution in original post

7 REPLIES 7
Super User IV
Super User IV

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

Try like

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


hwoehler Helper I
Helper I

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

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

Super User IV
Super User IV

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

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

Screenshot 2020-02-28 22.25.08.png





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


hwoehler Helper I
Helper I

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

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

Unbenannt2.PNG

 

Best regards,hwoehler

Microsoft
Microsoft

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

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 Helper I
Helper I

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

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.

Highlighted
Super User IV
Super User IV

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

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

 

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors