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

macfuller

Active Member
Joined
Apr 30, 2014
Messages
317
Office Version
  1. 365
Platform
  1. Windows
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.

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

The measure I want to create is
Code:
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
I don't know DAX in anyway but should it be this? >
MAX ( 'Calendar'[Date] ) <= [Production Start Date [Date]],
 
Upvote 0
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
Code:
=B$1*(1+$E$1/12)^A2
that's testing your formula, where you have (1+inflation rate/12)^months
In C2 write
Code:
=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.
 
Upvote 0
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.
 
Upvote 0
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:
    Code:
    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
 
Last edited:
Upvote 0
Don't use the MAX measure on dates...use LASTDATE:

MAX ( 'Calendar'[Date] )

becomes:

LASTDATE('Calendar'[Date])

And FYI the MIN equivalency is FIRSTDATE.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top