Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Date | Value |
01.01.2021 | 100 |
02.02.2021 | 200 |
01.03.2022 | 300 |
01.03.2022 | 580 |
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:
This is what I get when I use the measure AddOneMonth:
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.
Solved! Go to Solution.
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.
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
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.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |