Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

YTD

Hi All, 

 

I'm trying to calculate YTD and make the values stop at EOMONTH (Today()) by default.= (i.e. without the month slicer being selected, it should show you YTD up till end of this month).  If a slicer is selected, then i want the YTD value to be as at the month  selected. i.e. sum of all sales until and including the month selected. 

 

My formula at the moment is 
Var YTD_Sales = TotalYTD(SUM(Sales[Amount]), DimDate[Date])

 

RETURN 

IF(MAX(DimDate[Date]) <= EOMONTH (TODAY(),0), YTD_Sales)

 

I get a BLANK() when no slicer is selected. I want, however, the card to still be populated and show YTD till EOM but then if i select a prior month, it will also show YTD up till and including month selected. 

 

Thanks in advance

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Anonymous Try this:

    var YTD_Sales=CALCULATE(SUM(Sales[Amount]),DATESYTD(DimDate[Date]))

    var YTD_Sales_Today=CALCULATE(SUM(Sales[Amount]),DATESBETWEEN(DimDate[Date],DATE(YEAR(TODAY()),1,1),EOMONTH(TODAY())))

    return IF(MAX(DimDate[Date]) <= EOMONTH (TODAY(),0), YTD_Sales,YTD_Sales_Today)

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymous Try this:

    var YTD_Sales=CALCULATE(SUM(Sales[Amount]),DATESYTD(DimDate[Date]))

    var YTD_Sales_Today=CALCULATE(SUM(Sales[Amount]),DATESBETWEEN(DimDate[Date],DATE(YEAR(TODAY()),1,1),EOMONTH(TODAY())))

    return IF(MAX(DimDate[Date]) <= EOMONTH (TODAY(),0), YTD_Sales,YTD_Sales_Today)

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      That worked perfectly! thank you so much!