Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Andrea_Jess 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)
@Andrea_Jess 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)
That worked perfectly! thank you so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |