DAX Formula for a date field

[Raul]

Hello Community!

I have the Calendar table related to the Table1 table by the field Calendar[Date] - Table1[Date] and related to the Table2 table by the field Calendar[Date] - Table2[Date].

I need a mesure to calculate the sum of values of the Amount field according to this condition:

• The amounts of the records in Table 1 for the period 01/01/selected year to 03/31/selected year plus the records in Table 2 for the period 09/01/selected year+1 to 12/31/selected year+1

And I create this measure:

Measure = 

VAR varYear = SELECTEDVALUE(Calendar[Year])

RETURN

CALCULATE(SUM(Table2[Amount]), ALL(Calendar), DATESBETWEEN(Table2[Date], DATE(varYear+1,09,01), DATE(varYear+1,12,31))) +

CALCULATE(SUM(Table1[Amount]), DATESBETWEEN(Table1[Date], DATE(varYear,01,01), DATE(varYear,03,31)))

But the measurement results are not correct because it returns records that do not match the condition I need. What is wrong with the DAX formula?

Thank you.

[Raul]

Hi @amitchandak,

Thank you for your comment but the result it's the same as with my formula. Any other suggestions?

[v-shex-msft]

Hi @Raul,

Can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[v-shex-msft]

HI @Raul,

Did amitchandak 's suggestion help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements find it more quickly.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak]

@Raul , Try like

Measure =
VAR varYear = SELECTEDVALUE(Calendar[Year])
RETURN
CALCULATE(SUM(Table2[Amount]), filter(Table2, Table2[Date]>= DATE(varYear+1,09,01) && Table2[Date]<= DATE(varYear+1,12,31))) +
CALCULATE(SUM(Table1[Amount]), filter(Table1,Table1[Date] >= DATE(varYear,01,01) && Table1[Date] <= DATE(varYear,03,31)))

or

Measure =
VAR varYear = SELECTEDVALUE(Calendar[Year])
RETURN
CALCULATE(SUM(Table2[Amount]), filter(Table2, Table2[Date]>= DATE(varYear+1,09,01) && Table2[Date]<= DATE(varYear+1,12,31)), all(calendar)) +
CALCULATE(SUM(Table1[Amount]), filter(Table1,Table1[Date] >= DATE(varYear,01,01) && Table1[Date] <= DATE(varYear,03,31)), all(calendar))

[Raul]

Hi @amitchandak and @v-shex-msft 

I solved my question by creating a variable for each option and sum both variables.
Thank you for your help.

[Raul]

Hi @amitchandak and @v-shex-msft 

I solved my question by creating a variable for each option and sum both variables.
Thank you for your help.

[v-shex-msft]

Hi @Raul,

I'm glad to hear you find the solution.😊

BTW, did you mind sharing a sample formula for this? I think it will help other users who were looking for a similar formula.

Regards,
Xiaoxin Sheng

[Raul · Accepted solution]

Of course:

Measure = 

   VAR varYear = SELECTEDVALUE(Calendar[Year])

   VAR varPVY = CALCULATE(SUM(Table2[Amount]), ALL(Calendar), DATESBETWEEN(Table2[Date], DATE(varYear+1,09,01), DATE(varYear+1,12,31)))

   VAR varACY = CALCULATE(SUM(Table1[Amount]), ALL(Calendar), DATESBETWEEN(Table1[Date], DATE(varYear,01,01), DATE(varYear,03,31)))

RETURN

   varPVY + varACY