DAX measure running slow - How to improve DAX measure performance

[databot_kd]

Hi Community, 

I am having issues with the performance of my dax measures. I am trying to calculate a opening balance/ running total upto the selected date minus 1 month.  
Hoping somone can provide  guidance on how to improve the performance of it. 

Below are the two DAX Measures 

DAX

ATD =

VAR EoMDate = EOMONTH(MAX('Calendar'[date]) , 0)

VAR App = CALCULATE(SUM('GL'[amount]),  'GL'[Date] <= EoMDate , 'GL'[data_source] = "glaph") + 0

Return App

OpenBalance =

VAR EomDate1 = EOMONTH(MAX('Calendar'[date]), -1)

VAR OpenBal = CALCULATE(SUM('GL [amount]),  'GL'[GLDate] <= EomDate1 , 'GL'[source] = "glhp") + [ATD]

RETURN Bal

                                                  

The selected date is from a calendar table which is linked to GL Table.

Thanks in advance

[Uzi2019 · Accepted solution]

hi @databot_kd 

VAR App = CALCULATE(SUM('GL'[amount]),  'GL'[Date] <= EoMDate , 'GL'[data_source] = "glaph") + 0

Remove +0 from measure it will unnecessary  add 0 value to add the levels.

i hope it may reduce query time.

[databot_kd]

Thanks @Uzi2019 seemed to make it run alot faster. 

[Uzi2019 · Accepted solution]

Hi @databot_kd 
You can also take filter function which return the query much faster rather than row by row check the condition.

CALCULATE(SUM('GL'[amount]),  'GL'[Date] <= EoMDate , 'GL'[data_source] = "glaph")

Try to take filter finction for this condition.

https://www.youtube.com/watch?v=jcOa4sEMDDs

Filter function works faster than without apply filter condition.

I hope I answered your question!

[Uzi2019 · Accepted solution]

hi @databot_kd 

VAR App = CALCULATE(SUM('GL'[amount]),  'GL'[Date] <= EoMDate , 'GL'[data_source] = "glaph") + 0

Remove +0 from measure it will unnecessary  add 0 value to add the levels.

i hope it may reduce query time.