Forum Discussion

Raul's avatar
Raul
Post Patron
4 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 ne...
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
     
     
amitchandak's avatar
amitchandak
Super User
4 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
Post 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.