Why get the different month # and day # results using CALCULATE() within DATE()?

[Anonymous]

The max [due_date] in a fact table is:

Here:

DAX_look_4_date = CALCULATE ( MAX ( ‘fact table’[due_date]), ALL ( ‘fact table’[project_due_dt],’fact table’[org_cate], ‘fact table’[organization_type], ‘fact table’[project_status]) )

I need the results for 2019-11-1, 2018-11-1, 2017-11-1,... and so on.

Why using the following I get 2019-11-30, 2018-11-30, 2017-11-30,... and so on results?

upto_date = date(year(MAX ( Date_dim[Date] )),month(CALCULATE ( MAX ( ‘fact table’[due_date]), ALL ( ‘fact table’[project_due_dt],’fact table’[org_cate], ‘fact table’[organization_type], ‘fact table’[project_status]) )),

day(CALCULATE ( MAX ( ‘fact table’[due_date]), ALL ( ‘fact table’[project_due_dt],’fact table’[org_cate], ‘fact table’[organization_type], ‘fact table’[project_status]) )))

But I can get 2019-11-1, 2018-11-1, 2017-11-1,... and so on results using the following:

upto_date =  date(year(MAX ( Date_dim[Date] )),11,1)

I don't want to manually input/change the month/day # every time.

What do I miss? Is this a bug?

[edhans · Accepted solution]

Just because the max due date in your fact table is Nov 1, 2019 doesn't mean the max date in your Dim_Date table is the same. It has its own dates, and unless you've changed filtering, your date table is not filtered by your fact table. If you want it to be for that measure, change it to add an additional function to your Calculate:

CROSSFILTER(datetable[date],facttable[date],both)

You could change crossfiltering to bi-directional in the model as well, but it isn't needed for this measure (or any measure for that matter if you use CALCULATE with CROSSFILTER, and bi-directional crossfiltering in the model introduces issues that can bite you if you aren't aware of them, so it is best avoided in general.

I would try and do it for you but I pasted your code into DaxFormatter and it isn't valid. The Post Code button in the advanced toolbar could help above so we can see exactly what the measure is.

[Anonymous]

Thank you.

When I use the following code, I can get the max date that I want from the Fact table, e.g. 2019-11-01 for Y2019.

DAX_look_4_date_cross = 
date(year(MAX ( Date_dim[Date] )), 
     month(CALCULATE ( MAX ( 'factTB'[project_due_dt] ), ALL ( 'factTB'[project_due_dt], 'factTB'[organization_type], 'factTB'[project_status]) )),
     day(CALCULATE ( MAX ( 'factTB'[project_due_dt] ), ALL ( 'factTB'[project_due_dt], 'factTB'[organization_type], 'factTB'[project_status]) )))

• Note: here the Year info is from the Date tabe; the Month and Day info is from the Fact table.
• If add [Year] column to the Visual, I can see I get the Max date for each year, e.g.

I want to do a YTD year-over-year comparison based on the current max date (20191101), that is: I want

• (20150101 - 20151101) vs (20160101 - 20161101) vs (20170101 - 20171101) vs (20180101 - 20181101) vs (20190101 - 20191101)

When I use the above code for the following, i.e.

cntd Value Each Year YTD_cross = 
    CALCULATE (
        [cntd], 
        DATESBETWEEN (
            Date_dim[Date],
            MIN ( Date_dim[Date] ),
date(year(MAX ( Date_dim[Date] )), 
     month(CALCULATE ( MAX ( 'factTB'[project_due_dt] ), ALL ( 'factTB'[project_due_dt], 'factTB'[organization_type], 'factTB'[project_status]) )),
     day(CALCULATE ( MAX ( 'factTB'[project_due_dt] ), ALL ( 'factTB'[project_due_dt], 'factTB'[organization_type], 'factTB'[project_status]) )))
                        )
                )

it only give me the comparison about:

• (20150101 - 20151221) vs (20160101 - 20161225) vs (20170101 - 20171221) vs (20180101 - 20181231) vs (20190101 - 20191101)

Here,

cntd = DISTINCTCOUNT('factTB'[project_code])

How to fix the Month # and Day # for a YTD comparison?

• For example, in this case, I just want to use the max date in 2019 from the Fact table for the month # (i.e. 11) and Day # (i.e. 1). How should I do?

[edhans]

CROSSFILTER, like USERELATIONSHIP, is a modifier for CALCULATE, so add it as the last "filter" in a CALCULATE function. It isn't really a filter, and the order doesn't matter (other than it cannot be before the CALCULATE expression field,) but I always put it after all other filters. See this for more info if interested in reading more.

What do you get after you add it to your CALCULATE functions?

[Anonymous]

Sorry.

I have updated my previous reponse. Please see the update...

What I want is like a YTD year-over-year comparison using the fixed Month # and Day # in according to the 2019 max date. The 2019 max date is 20191101, then:

• (20150101 - 20151101) vs (20160101 - 20161101) vs (20170101 - 20171101) vs (20180101 - 20181101) vs (20190101 - 20191101)

[Anonymous]

When I use the following code, I can get the max date that I want from the Fact table, i.e. 2019-11-01 for 2019.

DAX_look_4_date_cross = 
date(year(MAX ( Date_dim[Date] )), 
     month(CALCULATE ( MAX ( 'factTB'[project_due_dt] ), ALL ( 'factTB'[project_due_dt], 'factTB'[organization_type], 'factTB'[project_status]) )),
     day(CALCULATE ( MAX ( 'factTB'[project_due_dt] ), ALL ( 'factTB'[project_due_dt], 'factTB'[organization_type], 'factTB'[project_status]) )))

• Note: here the Year info is from the Date tabe; the Month and Day info is from the Fact table.
• When I add [year] from the Date table, I get the max dates for each year.

When I use the above code for the following, I actually get the count distinct info from YYYY-01-01 to the MAX date from the Fact table for each year.

cntd Value Each Year YTD_cross = 
    CALCULATE (
        [cntd], 
        DATESBETWEEN (
            Date_dim[Date],
            MIN ( Date_dim[Date] ),
date(year(MAX ( Date_dim[Date] )), 
     month(CALCULATE ( MAX ( 'factTB'[project_due_dt] ), ALL ( 'factTB'[project_due_dt], 'factTB'[organization_type], 'factTB'[project_status]) )),
     day(CALCULATE ( MAX ( 'factTB'[project_due_dt] ), ALL ( 'factTB'[project_due_dt], 'factTB'[organization_type], 'factTB'[project_status]) )))
                        )
                )

• (20150101 - 20151221) vs (20160101 - 20161225) vs (20170101 - 20171221) vs (20180101 - 20181231) vs (20190101 - 20191101)

Here,

cntd = DISTINCTCOUNT('factTB'[project_code])

I want fixed Month # and Day # in according to the 2019 max date for the YTD year-over-year comparison. The 2019 max date is 20191101, then:

• (20150101 - 20151101) vs (20160101 - 20161101) vs (20170101 - 20171101) vs (20180101 - 20181101) vs (20190101 - 20191101)

How should I do?