Forum Discussion

Raul's avatar
Raul
Icon for Post Patron rankPost Patron
5 years ago
Solved

DAX Formula for a date field

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.

Need Help
  • Raul's avatar
    Raul
    4 years ago

    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
     
     

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Raul
      Icon for Post Patron rankPost Patron
      4 years ago

      Hi amitchandak and Anonymous 

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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's avatar
      Raul
      Icon for Post Patron rankPost Patron
      3 years ago

      Hi amitchandak and Anonymous 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

  • Raul's avatar
    Raul
    Icon for Post Patron rankPost Patron
    5 years ago

    Hi amitchandak,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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