Forum Discussion

chahineatallah's avatar
chahineatallah
Icon for Helper III rankHelper III
3 years ago

Date calculations in dax without using time intelligence

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

DAX

1 Reply

  • JW_van_Holst's avatar
    JW_van_Holst
    Icon for Resolver IV rankResolver IV
    3 years ago

    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.