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

     

     

hwoehler's avatar
hwoehler
Helper I
6 years ago

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

amitchandak's avatar
amitchandak
Super User
6 years ago

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

  • hwoehler's avatar
    hwoehler
    Helper I
    6 years ago

    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's avatar
      amitchandak
      Super User
      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))