Measure can't decide if it's a date??

[macfuller]

I've got a date measure that works with some operators but not others. Perhaps someone can tell me what I'm doing wrong in formatting? I get an error that the date measure is a text measure and DAX cannot compare text and dates, but only for certain operations.

I've got an inflation formula that I want to apply to future months in a forecast plant operation. However, I only want to apply it after production starts (e.g. April 2019) rather than when the project (and calendar table) starts (e.g. August 2018). So for months prior to May 2019 the index would be 1, then it increases according to the inflation rate entered by the user.

The user enters the Production Start Date on the spreadsheet in a table with multiple default values for the model. PowerQuery pulls the table into the model with the column as a text value, and I use this formula to obtain the measure.

Production Start Date:=FORMAT (
    CALCULATE (
        MAX ( tblDefaults[Value] ),
        tblDefaults[Item] = "Production Starts"
    ),
    "Short Date"
)

The measure I want to create is

Inflation Index :=
IF (
    MAX ( 'Calendar'[Date] ) <= [Production Start Date],
    1,
    CALCULATE (
        POWER ( 1 + ( [Default Inflation Rate] / 12 ), [Months from Start] ),
        FILTER ( 'Calendar', 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
    )
)

But I get the error on the first IF clause. However, if I use

DATEDIFF([Production Start Date], MAX(Calendar[Date], MONTH)

DAX will recognize [Production Start Date] as a date. I can't use DATEDIFF in my first IF clause however since it can return negative values when the calendar dates in the pivot table are earlier than the start of production.

The Calendar[Date] field is generated in PowerQuery with a data type of Date, and it shows as a Date in the data model as well.

Multiplying [Production Start Date] * 1 will allow it to work in the Inflation Measure, but I'd like to get the measure to the correct type so I don't have to remember the workaround.

 

[mole999]

I don't know DAX in anyway but should it be this? >
MAX ( 'Calendar'[Date] ) <= [Production Start Date [Date]],

 

[Andrew_UK]

Your maths is also slightly wrong. Setup an Excel sheet and test out how you've written your formula vs how you should have written your formula.

In column A go 0...36, in B1 and C1 write 100, in E1 write 0.05 (we'll test realistic inflation rates first)

In B2 write

=B$1*(1+$E$1/12)^A2

that's testing your formula, where you have (1+inflation rate/12)^months
In C2 write

=C$1*(1+$E$1)^(A2/12)

that's how it should be written

So if something is increasing by 5% yoy and starts at 100, then we'd expect it to say 105 after 1 year. In your formula it's actually at 105.12, small differences

But what if the inflation rate is 50%? After 1 year we'd expect 1.5, but yours shows 163.21!!

I can't really help with your DAX problems. Format isn't a formula I've ever bothered learning since you can just set the formats just as easily at the top of the page. I'd guess that to avoid the negatives you talked about with the DateDiff, then you could try using MAX(0,[Measure]) - one of the big developments in 2016 was that you could enter scalars in within the min/max fields and doing this means that it will read zero if your measure is negative. Alternatively if you want it to just read blank that instance then you could just use a normal if statement.

 

[macfuller]

For Mole999 the Production Start Date is a measure, but I can understand why you'd think it's a date column in a table.

Andrew_UK, yes, I get $163 rather than $150 at 50%. I attribute the slightly higher values after a year to monthly compounding rather than annual. What I had read online for calculating loan payments had the interest rate divided by 12 rather than the exponent as you have done so I used the same methodology. According to Investopedia the effective annual interest rate increases with the number of payments.

In the real world wage increases and inflation would probably be assessed once a year, but the model provides monthly breakouts and frankly I didn't want to spend the time in figuring out how to write the measure to only increment annually with everything else that had to be done <g>. Your formula will apparently give an effective annual rate of whatever rate is entered but it will still accrue monthly rather than being applied once a year. I'll have to throw a MODE calculation in there at some point with just an annual boost to more closely model the expected behavior when I get the time.

 

[Ozeroth]

Hi there,
I'm guessing the original problem of getting [Production Start Date] to be type date comes from the fact that the tblDefaults[Value] column contains multiple data types, so probably ends up being a column of type text (or an undefined type). Also, the FORMAT function produces a text value in a date format, which may or may not be interpreted as a date depending where it is used.

My suggestions are:

1. Use the DATEVALUE function to ensure a date is returned, assuming the underlying value is in a valid date text format:
   

   Production Start Date :=
   DATEVALUE (
       CALCULATE (
           MAX ( tblDefaults[Value] ),
           tblDefaults[Item] = "Production Starts"
       )
   )

2. For the tblDefaults table in the data model, unpivot the tblDefaults[Item] column so that you have a single-row table with one column per default value, with each column being the appropriate type.
   The original table in Excel can stay in the existing format for ease of data entry, perhaps with a "type" column added that is then used in Power Query to set the type of each column in the final table.

Regards,
Owen

 

[bkjohn2016]

Don't use the MAX measure on dates...use LASTDATE:

MAX ( 'Calendar'[Date] )

becomes:

LASTDATE('Calendar'[Date])

And FYI the MIN equivalency is FIRSTDATE.