DATEADD and end of the month

[nightfall]

Hi all,

new day new silly question.

I have a table showing the value of a given account at the end of each month. So for each account there is a value on the 31 of January, on the 28 of February and so on.

I wanted to create a measure that changes the filter context for the date to the next month, so that on february 28 will be shown the value of jan 31, and so on.

I thought that merely using this code would be enough,

CALCULATE(
    [Budget con pesi],
    DATEADD(
        'Calendar'[Date],
        -1,
        MONTH
    )
)

however, the output is not what i expected:

In the documentation, it is stated that

• The result table includes only dates that exist in the dates column.

• If the dates in the current context do not form a contiguous interval, the function returns an error.

However, the dates column is the usual calendar table. is it because the dates in the fact table are not contiguous in the interval?

Do you have any hints on how to solve this issue?

Thank you and best regards,

Vittorio

[Greg_Deckler]

@nightfall I would use EOMONTH instead of DATEADD. This is why I don't recommend TI functions.

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[nightfall]

Hi @Greg_Deckler thank you for you input.

I was actually looking into the possibility of using EOMONTH, however i would appreciate to know the reson of this outcome.. do you have an hint on why technically this happens? 

Thank you and best regards,

Vittorio

[Greg_Deckler]

@nightfall Given the complexity and nuance of DATEADD, it is difficult to say exactly. I would need your data and data model to replicate and see what is going wrong. Again, I would stick to using EOMONTH to grab your max date. Then just FILTER between the dates you want.

[aj1973]

Hi @Greg_Deckler 

Where is the mistake or the difference between the 2 ways that would pushe not to recommend TI Functions!!

[Greg_Deckler]

@aj1973 You must have not read this one: https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

[aj1973]

No I haven't, I was just an end user who clicked on filters in a report built by a developer to viusalize the difference. No difference at all.

If you don't have time to explain with 2 words thats fine, but I wouldn't think it wise to ask the end user to read 2 pages to understand a thing that he can't see or feel.

[Greg_Deckler]

@aj1973 Two words "TI bad". Bottom line, there is nothing that TI functions do that can't be done using other DAX functions. The things that TI functions do are strange in many respects and it is easy to use them incorrectly and get weird results. So, stick to basic DAX equivalents that don't produce strange results like TI functions often do. Easier to understand and debug. And all of the equivalents for every TI function are included in the link, including just how bizarre DATEADD can be. Control your code, don't let your code control you.

[aj1973]

Hi @nightfall 

The end of month column, add it to the calendar table instead of your fact table then use the column in the visual. It should then work.

[nightfall]

hello @aj1973  thank you for you inputs.

Would you mind elaborating your suggestion? I am a bit slow 😞

why the relationship existing between the column in the fact table and the calendar table is not enough?

Thank you again!

Best regards,

Vittorio

[aj1973]

@nightfall 

I didn't say that the relationship is not enough, the relationship must exist.

DATEADD is a Time Intelligence function, therefore a date table is mandatory in the model especially when the date column in the fact table is left with Gaps, like yours. So in order to get accurate values with Time intelligence functions you need to add a calendar tabel, add all columns that you need for your calculations, and use the calendar fields in the visuals. 

[nightfall]

Thank you for your explanation.

Do you mean that i should add to the calendar table a new column including only the dates 31/1/2022, 28/2/2022, ... ??

Because all these dates are already present in the relevant calendar column! Do you have any idea on why using the "usual" relationship ends up in this mismatch?

Thank you! 

VIttorio

[nightfall]

Hello @Anonymous 

thank you for your prompt reply.

The calendar is correctly marked.

Putting as date in the fact table the first day of the month works, however i preferred to keep the last day of the month for visualisation and for logical reason (it's representing the total of the current month, so it should be at the end of the month).

Would you mind explaining what do you mean with "filtering only by month"?

Thank you again,

Vittorio

[Anonymous]

Double check your calendar is marked as date table. Then check if filtering only by month does the trick. If nothing works try moving the date to the first day of the month, then jumping one month, then going again to the end of the month