Forum Discussion
Helper IIICalculating (Sum) when using a between date slicer: Calculate between values only
Hi there,
I want to display the total value for a month by using the between slicer.
However, when I try and make it easier for the user so the user can select from the first of the montht the 1st of the second month I gives me the total for both months.
Any ideas to change measure or do I have to change my approach?
I will use the same page to insert SAMEPERIODASLATYEAR and add an KPI card.
Many thanks.
Rgds,
Niel
- Do you just want them to select a single month? If so I suggest using a Month slicer, with the month from your DimDate table if you have one:
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html niel_orvyn1 , Try to have separate date table Try
measure =
var _max = eomonth(maxx(allselected(Date),Table[Date]),0)
var _min = eomonth(minx(allselected(Date),Table[Date]),-1)+1return
calculate(sum(Tbale[value]),filter(all(Table[Date]), Table[Date] >=_min && Table[Date] <=_max))
measure =
var _max = eomonth(maxx(allselected(Date),Table[Date]),0)
var _min = eomonth(minx(allselected(Date),Table[Date]),-1)+1return
calculate(sum(Tbale[value]),filter(all(Date[Date]), Date[Date] >=_min && Table[Date] <=_max))So time this can group data into few dates. if that happens refer to this video
3 Replies
- AllisonKennedy
Community Champion
Do you just want them to select a single month? If so I suggest using a Month slicer, with the month from your DimDate table if you have one:
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
niel_orvyn1AllisonKennedy and amitchandak
Many thanks for both your replies and help. Adding a dimdate table solved my problem, previously I was using the date from the original table😞
Best Regards,
Niel
- amitchandak
Super User
niel_orvyn1 , Try to have separate date table Try
measure =
var _max = eomonth(maxx(allselected(Date),Table[Date]),0)
var _min = eomonth(minx(allselected(Date),Table[Date]),-1)+1return
calculate(sum(Tbale[value]),filter(all(Table[Date]), Table[Date] >=_min && Table[Date] <=_max))
measure =
var _max = eomonth(maxx(allselected(Date),Table[Date]),0)
var _min = eomonth(minx(allselected(Date),Table[Date]),-1)+1return
calculate(sum(Tbale[value]),filter(all(Date[Date]), Date[Date] >=_min && Table[Date] <=_max))So time this can group data into few dates. if that happens refer to this video