Month Selection

[BI_Dev]

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,

[cristianj23a]

Hello, you can do it in set analysis (measurements)

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.

[BI_Dev]

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.

[cristianj23a]

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.

[BI_Dev]

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.

[BI_Dev]

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.