topic Re: END OF MONTH does not work with date table but does with fact table in DAX Commands and Tips

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

akhaliq7 — Fri, 16 Sep 2022 10:10:29 GMT

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])

)

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

johnt75 — Fri, 16 Sep 2022 10:48:28 GMT

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

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

Greg_Deckler — Fri, 16 Sep 2022 11:11:50 GMT

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-TITHW/m-p/434008

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.

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

akhaliq7 — Fri, 16 Sep 2022 11:15:44 GMT

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

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

akhaliq7 — Fri, 16 Sep 2022 11:40:42 GMT

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