Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |