CODEWITHSUNDEEP
excel-formulapowerbidax
Serdia
Serdia

Reputation: 4428

Displays incorrect values after created Date Dimension table in Power BI

My Fact table "Premiums" has Premium value and DateTime column (EffectiveDate). If I look at the Premium by MONTH(EffectiveDate) - it looks correct. Sorry, its not sorted.

enter image description here

But then if I create table Dates, join it to Premiums table on EffectiveDate column and try to look at Premiums by Month - it looks completely different.

enter image description here

enter image description here

I tried two different ways to create Calendar table. Still the same incorrect values.

What am I doing wrong?

Upvotes: 1

Views: 656

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

It looks to me like you are trying to match a datetime (Premiums[EffectiveDate]) to a date (Dates[Date]), so it's not matching most of your rows.

Try adding a calculated column to your Premiums table that converts EffectiveDate to a date type and then match that up with your calendar table. The following are a few ways that should work for your calculated column:

Calculated Column = Premiums[EffectiveDate].[Date]
Calculated Column = EDATE(Premiums[EffectiveDate],0)
Calculated Column = ROUNDDOWN(Premiums[EffectiveDate],0)
Calculated Column = DATE(YEAR(Premiums[EffectiveDate]),MONTH(Premiums[EffectiveDate]),DAY(Premiums[EffectiveDate]))
Calculated Column = DATEVALUE(FORMAT(Premiums[EffectiveDate],"yyyy/mm/dd"))

Edit: If you don't need the time part of the EffectiveDate column for anything, then just convert both that column and the calendar date column to date types, and it should match up.

Upvotes: 1

Related Questions