Dax Help

[Naveen29]

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 2022	Sales	Estimates
1-Jan	100
1-Feb	200
1-Mar	300
1-Apr	500	400
1-May	700	500
1-Jun	800	600
1-Jul	900	700
1-Aug	1050	800
1-Sep	1180	900
1-Oct	1310	1000
1-Nov	1440	1100
1-Dec	1570	1200

[Naveen29]

@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 · Accepted solution]

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]

@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))

[amitchandak]

@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"))