Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
Hi,
Share the link from where i can download your PBI file.
Below is the link to the .pbix file. Thank you!
https://drive.google.com/open?id=1BHItfqWD99x4wuWOPoDvXRdbcM5_Hp9f
User | Count |
---|---|
100 | |
89 | |
82 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |