Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
BI_Dev
Creator II
Creator II

Month Selection

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,

Labels (6)
5 Replies
cristianj23a
Partner - Creator III
Partner - Creator III

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.

https://www.linkedin.com/in/cristianjorge/
Do not forget to mark as "Accepted Solution" the comment that resolves the doubt.
BI_Dev
Creator II
Creator II
Author

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
Partner - Creator III
Partner - Creator III

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.

https://www.linkedin.com/in/cristianjorge/
Do not forget to mark as "Accepted Solution" the comment that resolves the doubt.
BI_Dev
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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.