Forum Discussion

Naveen29's avatar
Naveen29
Icon for Helper II rankHelper II
4 years ago
Solved

Dax Help

I would need help on DAX . Here is my sample data. The output of the DAX should return : Examples (User looking at data in April) April YTD = Jan to Mar Sales+ April Estimates from Estimates (U...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Naveen29 ,

     

    Try this code.

    Measure = 
VAR _SelectDate = SELECTEDVALUE('Table'[Year 2022])
VAR _DATERANGE = CALENDAR(DATE(2022,01,01),DATE(2022,03,31))
VAR _Sales = IF(_SelectDate in _DATERANGE,CALCULATE(SUM('Table'[Sales]), FILTER(ALL('Table'),'Table'[Year 2022]<= _SelectDate)),CALCULATE(SUM('Table'[Sales]), FILTER(ALL('Table'),'Table'[Year 2022] IN _DATERANGE)))
VAR _Estimate =CALCULATE(SUM('Table'[Estimates]),FILTER(ALL('Table'),'Table'[Year 2022]<= _SelectDate && NOT('Table'[Year 2022] IN _DATERANGE)))
RETURN
_Sales + _Estimate

    Result is as below.

    May YTD = Jan to Mar Sales+ April & May Estimate from Estimates

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Naveen29 , If you do not have estimates before April. Then you can use datesytd with a date table. Hope you have a date in the table

 

YTD Sales = CALCULATE(SUM(Sales[Sales]),DATESYTD('Date'[Date],"12/31"))

 

YTD Estimate= CALCULATE(SUM(Sales[Estimate]),DATESYTD('Date'[Date],"12/31"))