Forum Discussion

shishir999's avatar
shishir999
Helper II
7 years ago
Solved

Data Trending based on dates

Hi,

 

I am trying to make a trend line chart, based on Start & Expired date. Below are the expected trend line and the data:

 

 

In my scenario, need to read my Start date Amount as ZERO always for respective ProjID and Expire_Date as

the Amount value. Also, in case there is another entry for the same ProjID with modified Start\Expiry date, then it should

read that value either in the same Line or as another line trend.

 

Could someone please help in it.

 

Thanks

Shishir G.

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Shishir,

     

    Please download the demo in the attachment.

    1. Create a date table. Don't establish any relationship.

    2. Add an index since there could be more than one project ID.

    3. Create a measure.

    Measure =
IF (
    MIN ( 'Calendar'[Date] ) = MIN ( 'Table1'[Start_Date] ),
    0,
    IF (
        MIN ( 'Calendar'[Date] ) = MIN ( Table1[Expire_Date] ),
        SUM ( Table1[Amount] ),
        BLANK ()
    )
)

    Data-Trending-based-on-dates

     

    Best Regards,
    Dale

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Shishir,

     

    Please download the demo in the attachment.

    1. Create a date table. Don't establish any relationship.

    2. Add an index since there could be more than one project ID.

    3. Create a measure.

    Measure =
IF (
    MIN ( 'Calendar'[Date] ) = MIN ( 'Table1'[Start_Date] ),
    0,
    IF (
        MIN ( 'Calendar'[Date] ) = MIN ( Table1[Expire_Date] ),
        SUM ( Table1[Amount] ),
        BLANK ()
    )
)

    Data-Trending-based-on-dates

     

    Best Regards,
    Dale