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.

     

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

amitchandak - thanks, My requriment is simple. the new measure should return sum of the sales from Jan to March + Estimates from April onwards .

 

Example

 

  • (User looking at data in April) April YTD = Jan to Mar Sales+ April Estimates from Estimates
  • (User looking at data in May) May YTD = Jan to Mar Sales+ April & May Estimate from Estimates
  • (User looking at data in June) June YTD = Jan to Mar Sales+ April & May & June Estimate from Estimates
  •  
  •  
Anonymous's avatar
Anonymous
Not applicable
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.