Forum Discussion

Naveen29's avatar
Naveen29
Helper 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
    • (User looking at data in May) May YTD = Jan to Mar Sales+ April & May Estimate from Estimates
Year 2022SalesEstimates
1-Jan100 
1-Feb200 
1-Mar300 
1-Apr500400
1-May700500
1-Jun800600
1-Jul900700
1-Aug1050800
1-Sep1180900
1-Oct13101000
1-Nov14401100
1-Dec15701200
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.

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super 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"))

  • Naveen29's avatar
    Naveen29
    Helper 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
    •  
    •  
    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      Naveen29 , like this

      YTD Sales = CALCULATE(SUM(Sales[Sales]),DATESYTD('Date'[Date],"12/31"), 'Date'[Date] <=eomonth(today(),0))+ CALCULATE(SUM(Sales[Estimate]),DATESYTD('Date'[Date],"12/31")'Date'[Date] > eomonth(today(),0))

    • 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.