Showing all projects with past, current year, and future spends

[Mjdrejza1722]

I am having difficulty showing all projects AND displaying values across three categories of spend... past, currrent year, and future.  The requirement is to have the table display all projects no matter when the spend occurred, but divide up the spends accordingly depending on which year I select in the year slicer provided. 

If I let the year slicer affect the table, then I get my values in the correct columns, but not all of the projects display--only those where I have spend in that year.  Similarly, if I turn off the slicer interaction, I keep all the projects, but I cannot seem to provide the correct DAX to divide the spends into those time periods correctly.

Here is what the basic table looks like and those projects' values should not be in the future column...they should be divided up between the past and current year columns because all the spends are in year 2022 or before.  There should be no value in the Future column.  I attempted to resolve having a complete list of projects by disabling the year slicer on this visual, and then dealing with the spend through DAX.  Is this the best way to approach these requirements?  Or should I let the slicer affect the visual, give me the correct values, but work the full project is through DAX.


Below is my "Future_Years_Spend" measure.  Note that there is no Calendar Table in this model.  The Year slicer simply uses the [DATE] column I have in the table (using [DATE].[Year] hierarchy).  Also, my DAX for the Past and Current Year are exactly the same, except that I use < and = instead of > (i.e., for the line indicated with <-<-<-<-).

3.3 Future_Years_Spend =

VAR SelectYear = SELECTEDVALUE('Final Spend'[DATE].[Year])

RETURN

Calculate(SUMX('Final Spend','Final Spend'[Amount])

, REMOVEFILTERS('Final Spend'[DATE], 'Final Spend'[DATE - Month])  //there is a month filter on the page as well

, REMOVEFILTERS('Final Spend'[DATE].[Year])  

, 'Final Spend'[DATE].[Year]>SelectYear <-<-<-<-

, 'Final Spend'[CATEGORY]<>"Spend Delta"  //this is a category I don't want in the spend calc

, 'Final Spend'[CATEGORY]<>"Month Budget")   //this is a category I don't want in the spend calc

Thank you, in advance, for your help.

[Mjdrejza1722]

Unfortunately, I cannot share the file for proprietary reasons.  I can attempt share sample data, but without all the columns and measures at play, not sure that it helps.   Do you see anything glaring in my DAX?  Is my SELECTEDVALUE function okay?  Does my use of REMOVEFILTERS and then applying new year filters make sense?  Ultimately, I cannot understand why my inequalities are not working without that slicer active... why I have  

'Final Spend'[DATE].[Year] > SelectYear

and I am getting spends from projects where the year of the DATES are from that selected year or earlier (i.e., 2022, 2021, etc).  Am I missing something obvious?

[ghoshabhijeet · Accepted solution]

@Mjdrejza1722 It's hard to say about the behaviour of the DAX without seeing the data. I have created some dummy data based on your requirement and I have created a date table (not sure why you haven't created).

Here is the output.

Also attached the PBIX file for your reference.

** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !

Good Luck 👍

[ghoshabhijeet]

@Mjdrejza1722 Thanks for posting your query.

If your data have dates and you need your results based on date, it's always recommended to have a date table. Would be eaiser to help, if you could provide some sample data or share the pbix file. Thanks !