Cumulative actuals showing for future dates

[Anonymous]

Hi All, 

Is there a way i can make my chart not show values for future dates? I want to add a line graph to the chart to show the Total Pipeline (which will just be a sum of Proposals + Delayed) and i want the line to show a forecast for future values too. However, i don't want the actual bars itself to populate for future dates. 

I've attached link to google drive with PBIX. Any help is appreciated. Thank you! 

Proposal PBIX 

[amitchandak]

@Anonymous , Try if these example measures can help

Cumm Sales =
var _max = maxx(allselected(Sales), 'Date'[Date]) // or //maxx(allselected(Sales), Sales[Sales Date])
return
CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date]) && date[Date] =_max))

Cumm Sales =
var _max = maxx(allselected(Sales), 'Date'[Date]) // or //maxx(allselected(Sales), Sales[Sales Date])
return
CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(Sales),Sales[Sales Date] <=max(Sales[Sales Date]) && date[Date] =_max))

[Anonymous]

Hi Amit, 

Thanks for that. Unfortunately, that didn't seem to work 😞 

[amitchandak]

@Anonymous , please find the attached file after signature

[Anonymous]

Hi Amit, 

Thanks for that. Is there a way i can make the x-axis only show the dates listed under ProposalSubmissionDate rather than sequentially till the max Proposal date?

When i change the formula to take in the ProposalDate instead of DimDate, the stacked bar chart becomes just a normal bar chart. 

Thanks in advance

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

Cumulative Delays =
VAR A =
    CALCULATE (
        COUNTROWS ( FILTER ( 'Sheet1', 'Sheet1'[Status] = "Delayed" ) ),
        FILTER ( ALL ( DimDate[Date] ), 'DimDate'[Date] <= MAX ( DimDate[Date] ) )
    )
VAR ProposalSubmissionDates =
    VALUES ( Sheet1[ProposalSubmissionDate] )
RETURN
    IF ( MAX ( DimDate[Date] ) IN ProposalSubmissionDates, A )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.