Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Finalemu
New Member

Time Intelligence functions working in reverse

I have beenw working with Dax for a couple of years now but I yesterday I stumbled on something that I have never seen before and honestly I am a bit confused about what is going on.

 

To test this I created a small fact table like follows:

DateValue
01.01.2021100
02.02.2021200
01.03.2022300
01.03.2022580

 

I then proceed to create a Date table in Power Pivot and linked the two tables on the Date columns. SO far so good and pretty standard. THe interesting things happens when I try to use functions like DATEADD or SAMEPERIODLASTYEAR as the seems to be working in reverse. The following measure for exmaple subtracts one month insteaf od adding it.

 

CALCULATE([SumValues];DATEADD(DateTable[Date]; 1 ; MONTH)) SumValues is defined as SUM('Table5 1'[value]).

 

This is the result I get in a Pivot table for the Sum which is correct:

 

Finalemu_2-1651132641512.png

 

This is what I get when I use the measure AddOneMonth:

 

Finalemu_1-1651132629373.png

 

As you can see instead of adding one month as described in the measure is subtracts one (The columns are from the date table not the fact table of course). The same happens with other Time Inteligence functions. They work, but in reverse. The strange thing is that this exact things works with other fact tables linked to the same date table. I was able to replicate this in different excel versions and also in Power Bi.

 

I just can't seem to understand why this happes.

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

This is working as its supposed. It is adding 1 month to the date from the date table and returning the sum, so for 1 December it is adding 1 month and returning the value associated with 1 January.

View solution in original post

3 REPLIES 3
Finalemu
New Member

Ohh my god you are right. I have no idea how I did not see this. Now I feel like an idiot.

happens to all of us 

johnt75
Super User
Super User

This is working as its supposed. It is adding 1 month to the date from the date table and returning the sum, so for 1 December it is adding 1 month and returning the value associated with 1 January.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.