Displaying sum of calculated backlog orderes in specific future month

[awolf88]

Dearest community, 

I'm convinced there is a pretty simple solution to what I'm trying to do here, yet haven't had success in any attempt of mine so far:

Within the sales dashboard I'm creating (sales and projection by month of 2022) I have 2 different Datasets:

1. actual sales

2. open orders (these include future orders - yet to be shipped - as well as back-log orders (Definition: Shipping date < Today)

I've created my stacked column chart already and it's working as a charm. 

Now I've also calculated the sum of backlog orders by definition:

= CALCULATE(sum(OpenOrders), 'Calendar'[Date]<DATE(2022, 2,20))

This also seems to be calculating just fine. The number matches the sum of backlog as it should. 

My goal now is to add the sum of calculated backlog order to the next month's projection of open orders (in our case: March). But I'm having trouble limiting the figure to show in "next month" only. I've tried everything from creating a new measure and "filtering" the total into specific month "3"/"March" and showing the other months as value 0, but with no success so far. 

Perhaps one of you wizards would carry an easy coding answer for me, please? 

This is what it currently looks like: And what I'm trying to achieve is having only one line for Orders back-log in the month of March.

Thanks in advance!

Best,

Alex

[tamerj1]

@awolf88 

You have to have a Month Number column in your Calendar table. If you have multiple years you should even use Year Month Number. 
You can try this code

MyMeasure =
VAR OrdersbackLog =
CALCULATE ( SUM ( sales[OpenOrders] ), 'Calendar'[Date] < TODAY () )
VAR ActualSales =
SUM ( Sales[Sales] )
VAR CurrentMonthNumber =
MONTH ( 'Calendar'[Date] )
VAR Result =
IF (
CurrentMonthNumber = MONTH ( TODAY () ) + 1,
ActualSales + OrdersbackLog,
ActualSales
)
RETURN
Result

Sorry for the bad format as I'm sending from my phone

let me know if it works

[awolf88]

Hi tamerj, 

Tried your solution and I like the approach to it. 
I'm having issue entering the my CalendarTable after the "Month()" option as listed in your suggestion. 

I'm having trouble grasping why a simple "calculate([OrderBackLog], 'Calendar'[Month]=3) wouldn't work to show nothing but values for month of March?

Best,

[tamerj1 · Accepted solution]

MyMeasure =
VAR OrdersbackLog =
CALCULATE ( SUM ( sales[OpenOrders] ), 'Calendar'[Date] < TODAY () )
VAR ActualSales =
SUM ( Sales[Sales] )
VAR CurrentMonthNumber =
MAX ( 'Calendar'[Month Number] )
VAR Result =
IF (
CurrentMonthNumber = MONTH ( TODAY () ) + 1,
ActualSales + OrdersbackLog,
ActualSales
)
RETURN

[awolf88]

I appreciate your rapid responses!

Your explanations totally make sense. 

Unfortunately I'm still getting the coding issue in the line of "currentmonthnumber"

[tamerj1]

@awolf88 

MAX ( 'Calendar'[Month Number] )

but it has to be the month number no the month name

[tamerj1]

You can hard code the month number. But this not the issue. If yiu use 

CALCULATE  ( [OrderBackLog], 'Calendar'[Month] = 3 ) Then it will return the same value in all the cells as CALCULATE replaces the filter with the new value 3

in order to control where exactly to apply this code you have to write some simple code lile IF statement. 
this what this code does. It reads the current month number in the Matrix row and compare it with the next month. The calculation will happen only at the row where the month number in the filter equals the month number of next month from TODAY()

[tamerj1]

Sorry my mistake. 
it should be

'Calender'[Month Number]

[awolf88]

Hi Tamerj1,

the measures for OpenOrders & Sales are very simple sum(Openorders) & sum(Sales). 

Best,

Alex

[tamerj1]

Hi @awolf88 

what are the measure code for Open Orders & Sales?