Time intelligence not working in column?

[Niels_T]

Hello,

I would like to add a new column with the following code:

Stock Niveau =

IF(EOMONTH(MAX('Calendar'[Date]),0) = (EOMONTH(TODAY(),0)),

CALCULATE(SUM('Stock Value'[Cost Amount (Actual)]), ALL('Stock Value')) + [Total Orders] + 'Sales Forecast (GS)'[Sales FC aan COGS]

)

I noticed, however, that this doesn't give me any result as it just returns a blank column.

When I try this as a measure it does work.

Can someone explain why it doesn't work and how I can make it work?

[Anonymous · Accepted solution]

Hi  @Niels_T ,

Whether the relationship between your two tables is set to Single.

Result:

You can change its relationship to Both.

Result：

Best Regards,

Liu Yang

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

[adrianc2]

I could be wrong but I think you have added only a "TRUE" condition to your formula. So if in your data the EOMONTH value for the MAX date in your Date column does NOT equal the EOMONTH for the current date, you haven't told Power BI what to do - therefore it returns blank.

IF ( 'This is true', 'Do this', 'Otherwise do this')

I think you're missing the 'Otherwise do this' part.

In summary you need to add a third value to your formula so it knows what calculation to carry out if the EOMONTH value for the MAX date in your calendar does NOT equal hte EOMONTH value for TODAY.

[Niels_T]

Thanks for the suggestion. I tried it out but I don't think this is the issue. I tested it out with blank().

Stock Niveau =

IF(EOMONTH(MAX('Calendar'[Date]),0) = (EOMONTH(TODAY(),0)),

[Total Orders] + 'Sales Forecast (GS)'[Sales FC aan COGS] + CALCULATE(SUM('Stock Value'[Cost Amount (Actual)]), ALL('Stock Value')),

BLANK()

)

So all months except current month should be blank, but instead all months are blank. So it seems that this particular way of time intelligence is not working?

I was thinking that it could be my relations, but it is just strange that it does work with a measure. 

[adrianc2]

Do you definitely have data in your dataset for April 2022? Could be the change in months that has thrown it out if you have only data up to and including yesterday (March 2022)

[Niels_T]

For the sake of not sharing sensitive data I changed the numbers but my table looks something like this:

% vs Year%	Sales	Forecast 2022	MonthYearNum
20%	xxx	xxx	202201
20%	xxx	xxx	202202
15%	xxx	xxx	202203
10%	xxx	xxx	202204
15%	xxx	xxx	202205
15%	xxx	xxx	202206
15%	xxx	xxx	202207
15%	xxx	xxx	202208
15%	xxx	xxx	202209
10%	xxx	xxx	202210
10%	xxx	xxx	202211
10%	xxx	xxx	202212

The MonthYearNum is used to connect to my date table. My date table contains also a column with MonthYearNum. 

I don't think this should be a problem for time intelligence right? As I do have a date column with all dates as well.

[adrianc2]

Instead of MAX how about trying SELECTEDVALUE.

So the adapted DAX from the last snippet you sent would be 

Stock Niveau =
IF(EOMONTH(SELECTEDVALUE('Calendar'[Date]),0) = (EOMONTH(TODAY(),0)),
[Total Orders] + 'Sales Forecast (GS)'[Sales FC aan COGS] + CALCULATE(SUM('Stock Value'[Cost Amount (Actual)]), ALL('Stock Value')),
 
BLANK()
)

[Niels_T]

Stock Niveau = 
IF(EOMONTH(SELECTEDVALUE('Calendar'[Date]),0) = (EOMONTH(TODAY(),0)),
        [Total Orders] + 'Sales Forecast (GS)'[Sales FC aan COGS] + CALCULATE(SUM('Stock Value'[Cost Amount (Actual)]), ALL('Stock Value')), 
        
        BLANK()
        )

I tried it and unfortunately no result 😞