Forum Discussion

Samhunt's avatar
Samhunt
Icon for Helper II rankHelper II
2 years ago
Solved

Need help with a dax formula

Hi Everyone, 

 

I used the DAX Formula below to create a measure that will count all active properties of my company. 


"Total No. Of Active Properties "For Measures" =
 VAR CurrentDate = MAX('Calendar Lookup'[Date])
 VAR ActiveProperties =
 CALCULATE(
    COUNTROWS('Active Properties'),
    ALL('Calendar Lookup'),
    'Calendar Lookup'[Date] <= CurrentDate,
    ISBLANK('Active Properties'[Deactivation Date])
    || 'Active Properties'[Deactivation Date]>= CurrentDate
 )
 RETURN
ActiveProperties"
 
It works well when I add it in a visual. 
 
But when I use it to create another measure, lets say Revenue per available property or Fixed costs per available property etc this messes up my results all visuals related to my Revenue Measure which is the following: 
Total Revenue Managed Properties =
CALCULATE(
    SUM('Sales Data'[Amount]),
    FILTER (
        'Properties',
         'Properties'[Revenue Category] = "Management Fee")
         )
   
 
All expenses seem to be ok and they are based on the formula below:
Total Expenses Managed Properties =
    SUM( Expenses[Net Cost])
 
So probably the first formula in some way messes up with the calculate function of the second formula
 
Will I need a different formula to count all active properties and be used in other measures?

Thank you for your time. 
 
Need Help
  • Samhunt's avatar
    Samhunt
    2 years ago

    Hello Greg_Deckler please ignore this, 

    Apparently I messed something else up. 

     

    Thanks for taking the time to reply to me 

3 Replies