Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
User | Count |
---|---|
86 | |
82 | |
68 | |
64 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |