END OF MONTH does not work with date table but does with fact table

[akhaliq7]

When I use the following dax statement it works with my sales fact table but doesn't with my date table.

doesn't work:

EOM_DTBLE =

CALCULATE(

    SUM('Table'[sales]),

    ENDOFMONTH('DATE'[Date])

)

does work:

EOM =

CALCULATE(

    SUM('Table'[sales]),

    ENDOFMONTH('Table'[date])

)

[akhaliq7]

ok after looking around in other help boards and on this board have understood that when using the end of month dax function with a date table there needs to be a corresponding date in the sales table with a sales value for it to show. As in my case there was not a corresponding sales date dated the 31/01/2022 therefore a blank showed. However when using end of month with a sales table it works so will be using the fact table in the future as it is more error proof

[Greg_Deckler]

@akhaliq7 I wouldn't use CALCULATE and I wouldn't use time intelligence functions. EOMONTH is a great alternative function. 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...

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[johnt75 · Accepted solution]

The first version will only find sales which happened on the last day of the month. What are you trying to do ?

[akhaliq7]

Yes you are right so the one with the date table only looks if a value is there on the last day of the month but the end of month with the sales table actually finds the last date in the month that has a sales value. was wondering can the date table not be modified to be able to use with the end of month function