Projected sales to end of fiscal year while having a filter for YTD

[Sibrulotte]

Hi, 

here is a simple model with transactions that incorporate Actual amounts and projected amounts.

A measure for actuals:

RDF = sum(Transactions[Montant_réel])

A measure for projections:

RDF_projetées = sum(Transactions[Montant projections])

And a measure to combine them as the year moves on:

RDF_REEL_PROJETEES =

var vderniere_date = calculate(max(Transactions[Date_report]), removefilters(), Transactions[Produit_ID]  <> blank() )

var vreel = [RDF]

var vProjete_futur = calculate([RDF_projetées], removefilters(), filter(Calendrier, Calendrier[Date] > vderniere_date))

var resultat = vreel + vProjete_futur

return

resultat

It seems that my last measure will take into account a filter on Calendrier[Date], which I do not want, hence the removefilters() in vProjete_futur. how do I achieve this?

Here is the pbix :

https://fondsftq.sharefile.com/public/share/web-s8e84f1a6850c4ad59e728ce272c6bdf4

Have a great weekend.

[elitesmitpatel]

Please share the expected outcome

[lbendlin]

You provided a whole data model

But it looks like only this table is relevant?

Can you indicate the expected outcome based on that table? Something like this?

[Sibrulotte]

Hi, yes in deed only the transaction table is relevent. I build this mock up for other tests also.

This would be my desired result with dates set to anything below October 1st. 

[lbendlin]

Create a measure that returns BLANK() for all dates up to september, and then the forecast value for october and november.

You will see a gap between the two lines. if you don't like that then amend the measure to return the actual value for october.

[Sibrulotte]

It's already what I have, the actuals + project,  in RDF_REEL_PROJETEES. It's overlapped with actuals (RDF):

But my problem is that when I select dates lower than the last dates in actuals from the master calendar, the futur values are not shown anymore for the RDF_REEL_PROJETEES even if i have a "removefilter" in the projected part.

[lbendlin]

Why do you have a date filter in the first place?

[Sibrulotte]

Because several other visuals on the same page have metrics that use a sameperiodlastyear(Date) and I need that Date filter to also make sure that we are only showing the current fiscal year.

[Sibrulotte]

@lbendlin thank you for your input on this topic. This back and forth is always constructive and helps make realisations about why you make decisions about visualisations, dax measures and modeling. 

If you have any other ideas on how to achieve an unfiltered or at least show future sales projection even if there is a selection done on master calender dates, I'm all ears.

[lbendlin]

What is the story you are trying to tell? Which insights should the report users be able to get from your report?

[Sibrulotte]

In the same page there is a card that shows actual and comparative. It's important to keep only june 1st to october 31st to see my year to date and comparatives of the same period last year (first photo). But it would be nice for the graph to show all projections (in this case I have projections until november- see second photo). 

[Sibrulotte]

Is there anyone else with good DAX skills that would like to take a jab at this?

I can't imagine I'm the only one that has ever wanted to show projections like this.