Plot a measure starting on today's date

[TD21]

Hi, I need help plotting a measure on a line chart with dates on the x-axis, and have the line starts with today's date.

Say, today is June 20th. Jan - May should be blank. The line should start in June, and the June value should be the cumulative total from beginning of time till June.

My data looks like this:

Project ID     Forecast   

001            08/16/19   

002            10/15/19

003            02/06/20... etc.

The measure is as follows:

Forecast_Cumulative = 

    if(

        MIN(Dates[Date]) <= CALCULATE( MAX([FORECAST_DATE]), ALL(Data)),

        CALCULATE(

            COUNT(Data[FORECAST_DATE]),

            USERELATIONSHIP(Dates[Date],Data[FORECAST_DATE]),

            FILTER(ALL(Dates),

            Dates[Date] <= MAX(Dates[Date]))))

The orange line in the picture is what I'm trying to plot. Thanks in advance for your help.

[TeigeGao · Accepted solution]

Hi @TD21 ,

Please refer to the following DAX query:

Forecast_Cumulative =
IF (
    MIN ( Dates[Date] ) <= CALCULATE ( MAX ( [FORECAST_DATE] ), ALL ( Data ) )
        && MIN ( Dates[Date] ) >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
    CALCULATE (
        COUNT ( Data[FORECAST_DATE] ),
        USERELATIONSHIP ( Dates[Date], Data[FORECAST_DATE] ),
        FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
    )
)

The result will like below:

Best Regards,

Teige

[TD21]

@TeigeGao  your solution worked beautifully. Thank you!

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[TD21]

@Ashish_Mathur 

Below is the link to the .pbix file. Thank you!

https://drive.google.com/open?id=1BHItfqWD99x4wuWOPoDvXRdbcM5_Hp9f