Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have two expressions that work well for calculating Year-to-Date totals, but I'm struggling to make them respond properly when I select specific months.
Sum({$<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(Max(Date), 'DD/MM/YYYY'))"},Year,Quarter,Month,Week>} Sales)
Sum({$<Date={">=$(=YearStart(Max(Date), -1))<=$(=AddYears(Max(Date), -1))"},Year,Quarter,Month,Week>} Sales)
Right now, these always show the full year accumulation (Jan through the max selected date). What I'd like is:
When no months are selected: Keep the current YTD behavior
When specific months are selected (e.g., Aug & Sept): Show the sum for just those selected months (Aug + Sept), not the cumulative total from January
Basically, I want the expressions to be show cumulative when nothing is selected, but show the actual sum of selected months when I make a month selection.
Has anyone solved this before? I'd really appreciate any guidance on how to modify these expressions to handle both scenarios.
Thanks in advance!
Hi, if you do a "Date(YearStart(Max(Date))", it will filter from the start of the year, you can filter from the month start using: Date(MonthStart(Min(Date))
Note that this will sum consecutive months, if you select april and june (somewhat rare selection), it will add may data.to void this there could be differnt solutions based on the laoded data, a common solution may be checking if ther is a month selected and use differnt expression based on that, like:
If(GetSelectedCount(MonthField), Sum(Sales), [CurrentExpressionForYTD])
If you need your measure to respond to your selection, you can't ignore this filter in your expression:
Could you do it with just a max(Year)?
If you don't have one, you can also try creating it from your Date field.
Year(FieldDate) as FieldYear
or
Year(Date(FieldDate)) as FieldYear
or
Year(Date#(FieldDate,'DD/MM/YYYY')) as FieldYear
So with a simple Max(Year), it should work.
sum({<FieldYear = {"$(=Max(FieldYear))"}>} Sales)
- Regards
Hi, if you do a "Date(YearStart(Max(Date))", it will filter from the start of the year, you can filter from the month start using: Date(MonthStart(Min(Date))
Note that this will sum consecutive months, if you select april and june (somewhat rare selection), it will add may data.to void this there could be differnt solutions based on the laoded data, a common solution may be checking if ther is a month selected and use differnt expression based on that, like:
If(GetSelectedCount(MonthField), Sum(Sales), [CurrentExpressionForYTD])
I made a slight adjustment to the expression, but it works as expected now. Thanks!