Forum Discussion

Arete's avatar
Arete
Helper I
3 years ago
Solved

Measure doesn't work when referencing Calendar table fields

Hi All! I have 2 tables, Projects and Calendar, that are linked by the Project's created date. When I create a measure to only include 2022 records it comes back with blank but when I remove it the field property sums:

 

CY_Total Budget = 
CALCULATE(
	SUM(Project[Budget__c]),
	'Project'[MPM4_BASE__Status__c] IN { "Open", "Delivered" },
    'Calendar'[Year] IN { 2022 }
)

 

Any help is much appreciated, slowly learning DAX and I'm sure I'm missing something stupid.

DAX
Measures
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but when I see [CreatedDate] column in Project table => I think the data type is Datetime, not date.

    I suggest, in power query editor, please change this to Date data type, not datetime data type.

    I hope this helps.

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but when I see [CreatedDate] column in Project table => I think the data type is Datetime, not date.

    I suggest, in power query editor, please change this to Date data type, not datetime data type.

    I hope this helps.

    • Arete's avatar
      Arete
      Helper I
      3 years ago

      I knew it was something dumb! They are both Date/Time but the time values in the Calendar table are all set to 12:00 so there were no matches. Thanks for the help!