Forum Discussion

sureshpydi's avatar
sureshpydi
Regular Visitor
7 years ago
Solved

create trend line from maximum to zero line chart

Hi Team,

 

Can any one help me to get a line from maxim value to Zero on Power Bi Chart. 

 

Here is my requirement, I have a column with dates on X-Axis and Values in Y-AXIS. As shown the image below, top Graph shows my values from a column (Caluculated values, Those are good.) But We got another requirement from Client, saying that we need a trend line from Starting date to Specific Date (This will be specific, This can be anything hardcoded and can be changed.) 

 

I have tried to split the difference and caluculated weekly values (Like date difference is 7 weeks, then caluaculate difference and based on the current date substact from max value). It was worked initially. But as there is few dates missig in the interval, it is failed and graph is not coming as stright line,

 

 

Can anyone help me on this.

  • dedelman_clng's avatar
    dedelman_clng
    7 years ago

    Hi sureshpydi.  Try this out:

     

    Trend Points =
VAR __MinDate =
    MINX ( ALL ( Sheet1 ), Sheet1[Date] )
VAR __MinValue =
    LOOKUPVALUE ( Sheet1[Value], Sheet1[Date], __MinDate )
VAR __MaxDate =
    MAXX ( ALL ( Sheet1 ), Sheet1[Date] )
VAR __MaxValue =
    LOOKUPVALUE ( Sheet1[Value], Sheet1[Date], __MaxDate )
RETURN
    IF (
        CALCULATE ( MIN ( Sheet1[Date] ) ) = __MinDate,
        __MinValue,
        IF ( CALCULATE ( MAX ( Sheet1[Date] ) ) = __MaxDate, __MaxValue, BLANK () )
    )

     

    You'll have to be sure to set the X-axis as "Continuous" instead of "Categorical" or you will only get the starting and ending points.  Also, depending on your actual data set, the "ALL" function might have to be tweaked (using KEEPFILTERS or the like).

     

    Hope this helps,

    David

3 Replies

    • sureshpydi's avatar
      sureshpydi
      Regular Visitor
      7 years ago

      Hi Xiaoxin Sheng,

       

      Thanks for the reply. I have tried this. But it was not worked for me. I need a line from Max to 0 using DAX query. Could you please let me know if it is possible with DAX?

       

      Thanks,

      Suresh

      • dedelman_clng's avatar
        dedelman_clng
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Hi sureshpydi.  Try this out:

         

        Trend Points =
VAR __MinDate =
    MINX ( ALL ( Sheet1 ), Sheet1[Date] )
VAR __MinValue =
    LOOKUPVALUE ( Sheet1[Value], Sheet1[Date], __MinDate )
VAR __MaxDate =
    MAXX ( ALL ( Sheet1 ), Sheet1[Date] )
VAR __MaxValue =
    LOOKUPVALUE ( Sheet1[Value], Sheet1[Date], __MaxDate )
RETURN
    IF (
        CALCULATE ( MIN ( Sheet1[Date] ) ) = __MinDate,
        __MinValue,
        IF ( CALCULATE ( MAX ( Sheet1[Date] ) ) = __MaxDate, __MaxValue, BLANK () )
    )

         

        You'll have to be sure to set the X-axis as "Continuous" instead of "Categorical" or you will only get the starting and ending points.  Also, depending on your actual data set, the "ALL" function might have to be tweaked (using KEEPFILTERS or the like).

         

        Hope this helps,

        David