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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sibrulotte
Helper IV
Helper IV

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

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 :
 
Have a great weekend.
11 REPLIES 11
elitesmitpatel
Solution Supplier
Solution Supplier

Please share the expected outcome

lbendlin
Super User
Super User

You provided a whole data model

lbendlin_0-1726277812806.png

But it looks like only this table is relevant?

 

lbendlin_1-1726277853582.png

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

 

lbendlin_2-1726277982170.png

 

 

 

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. 

 

Sibrulotte_0-1726408941165.png

 

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.

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

Sibrulotte_0-1726437121522.png

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.

 

Sibrulotte_1-1726437227388.png

 

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

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.

@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.

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

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_1-1726669828125.png

 



Sibrulotte_0-1726669786363.png

 

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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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