Calculated Measure with Stop IF in Date Hierarchy

[Anonymous]

I am trying to create a  line graph with two measures - one summarizing past sales, and one forecasting future sales.  I am pulling in a data tables for both, and I have a measures calculating the period, and cumulative for both.

In order to make sure that the two measures meet on the same date (past sales does not have data after today, and forecast sales does not have data before today), I added an IF statement to the measures, to tell them to stop calculating before or after today, respectively. 

Past Sales = 
var MaxDate = MAX('Date Table'[Date])
var PastSales = 
    SUM('Past Sales Table'[Sales Dollars])
RETURN
IF(MaxDate <= TODAY(), PastSales , BLANK())

Forecast Future Sales = 
var MaxDate = MAX('Date Table'[Date])
var ForecastSales = 
    SUM('Forecast Sales Table'[Forecast Sales Dollars])
RETURN
IF(MaxDate >= TODAY(), ForecastSales , BLANK())

The problem is, after adding this IF statment, the past sales measures (both period and cumulative) stop showing data in the date hierarchy of the current period.  So for example, I am a couple weeks into October now, and when I look at my measures in a table showign the Year - Month - Day hierarchy, I can see past sales data up to today, at the day level.  But the current month, and current year, of the date hierarchy, are blank for the Past Sales.  The Forecast Future sales does summarize in the current month and year in the hierarchy - it is just the past, that somehow the IF statement is is returning the blank option, for those levels of the hierarchy.  

In the picture below, the cells I highlighted red are the problem.  For the past data, the IF statement is returning a blank, but I want it to return the to-date totals for the current month and current year, in those level of the date hierarchy.  When I comment out the IF statement, this problem goes away, and the to-date totals populate in the month and year correctly.  So, I know the IF statement is the problem, I just don't know why, or how to correct. 

[v-xiaosun-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here is my solution.

You can use CALCULATE function. I made a sample, and you can refer to these steps.

Create two measures.

Past Sales =
CALCULATE (
    SUM ( Tabelle1[sales] ),
    FILTER ( 'DATTE', 'DATTE'[Date] <= TODAY () )
)

Forecast Future Sales =
CALCULATE (
    SUM ( Tabelle1[sales] ),
    FILTER ( 'DATTE', 'DATTE'[Date] >= TODAY () )
)

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.