Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - I have historic data which updates once in a month and new months gets added to the Date column.
Ex:
ID | Date | Sales |
1 | 1/1/2023 | 120 |
1 | 1/2/2023 | 200 |
1 | 1/3/2023 | 300 |
1 | 1/4/2023 | 200 |
when I select Jan to Feb, 1/1/2023 and 1/2/2023 months should get selected and when I select Feb to March, 1/2/2023 and 1/3/2023 should get selected.
Thank you,
Sum({<Date= {'$(=MonthStart(Max(Date)))'}, Date= {'<=$(=Max(Date))'}>} Sales) -
Sum({<Date= {'$(=MonthStart(AddMonths(Max(Date), -1)))'}, Date= {'>=$(=MonthStart(AddMonths(Max(Date), -1)))<=$(=MonthEnd(AddMonths(Max(Date), -1)))'}>} Sales)
Regarts.
Hi - Thank you but I am looking for a filter to use,,,, when I select Jan to Feb, 1/1/2023 and 1/2/2023 months should get selected and when I select Feb to March, 1/2/2023 and 1/3/2023 should get selected.
try this:
CurrentMonth:
Let vCurrentMonth = Month(Today());
Sum({<Date = {'$(=MonthStart(Max(Date)))'},Date = {'<=$(=Max(Date))'}>} Sales)
-
If(
GetSelectedCount(Date) = 1,
Sum({
<Date = {'$(=MonthStart(AddMonths(Max(Date), -1)))'},
Date = {'>=$(=MonthStart(AddMonths(Max(Date), -1)))<=$(=MonthEnd(AddMonths(Max(Date), -1)))'}>
} Sales),
Sum({
<Date = {'$(=MonthStart(AddMonths(Max(Date), -$(vMesActual))))'},
Date = {'>=$(=MonthStart(AddMonths(Max(Date), -$(vMesActual))))<=$(=MonthEnd(AddMonths(Max(Date), -$(CurrentMonth))))'}>
} Sales)
)
Regarts.
Hi - Thank you but I am looking for a filter to use,,,, when I select Jan to Feb, 1/1/2023 and 1/2/2023 months should get selected and when I select Feb to March, 1/2/2023 and 1/3/2023 should get selected.
I tried the below..but did not work
if(year(Date) = year(today()) and match(num(month(Date)),'1','2'),'Jan-Feb'& year(today()),
if(year(Date) = year(today()) and match(num(month(Date)),'2','3'),'Feb-Mar'& year(today()),
if(year(Date) = year(today()) and match(num(month(Date)),'3','4'),'Mar-Apr'& year(today()))))
Jan-Feb WORKS, bur Feb-Mar only filters for March.