Date calculations in dax without using time intelligence

chahineatallah — Sun, 19 Mar 2023 06:28:01 GMT

hello powerbi/power pivot community

Am practicing custom date functions like writing my own date functions rather than builtin, so i have a question regarding previous month calculations, I wrote the below formula for previous month, so below formula worked

CALCULATE([Total sales],FILTER(ALL('Calendar'),'Calendar'[Date]>=EOMONTH(MAX('Calendar'[Date]),-2)+1 && 'Calendar'[Date]<=EOMONTH(MAX('Calendar'[Date]),-1)))

my question why when i write below without MAX function, i get blank , as i understand Calendar[Date] should take filter context which i have in my table (for example year 2003 and january),

CALCULATE([Total sales],FILTER(ALL('Calendar'),'Calendar'[Date]>=EOMONTH('Calendar'[Date],-2)+1 && 'Calendar'[Date]<=EOMONTH('Calendar'[Date],-1)))

am i missing something/misunderstanding? Thanks

below is a snapshot for easy reference

Re: Date calculations in dax without using time intelligence

JW_van_Holst — Sun, 19 Mar 2023 12:55:18 GMT

I'm suprised you don't get an error. EOMONTH needs a scalar function as input. You should expect an error message like: A single value for column 'Date' in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

topic Re: Date calculations in dax without using time intelligence in DAX Commands and Tips

Date calculations in dax without using time intelligence

Re: Date calculations in dax without using time intelligence