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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.