Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TD21
Helper II
Helper II

Plot a measure starting on today's date

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.

Chart2.jpg

1 ACCEPTED SOLUTION
TeigeGao
Solution Sage
Solution Sage

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:

PBIDesktop_hR6Si61lHC.png

Best Regards,

Teige

View solution in original post

4 REPLIES 4
TeigeGao
Solution Sage
Solution Sage

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:

PBIDesktop_hR6Si61lHC.png

Best Regards,

Teige

@TeigeGao  your solution worked beautifully. Thank you!

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur 

 

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.