5 Replies Latest reply on Aug 31, 2018 7:23 AM by Okechukwu Ossai

# Show this month and last month sale in column with a parameter selected

Hi,

With Superstore sample data, I want to list this month sale and last month sale in the column for different region when I select a special date with parameter. How?

Thanks,

Linda

• ###### 1. Re: Show this month and last month sale in column with a parameter selected

Hi Linda,

There are different ways to do this. It depends on what you want to do with the results. I did separate calculations for month sales, so they can be used as inputs in other calculated fields if required.

I assumed that Month Parameter are string calendar month names. See attached workbook.

The solution has 2 options;  either calculate 'This Month' and 'Last Month' as Dimensions or as Measures.

Create [Month Parameter]

Create calculated field [Selected Month]

IF DATETRUNC('month', [Date]) = DATEPARSE("dd/MMMM/yyyy",  "01" + "/" + [Month Parameter] + "/" + STR(YEAR(TODAY())))

OR DATETRUNC('month', [Date]) = DATEADD('month', -1, DATEPARSE("dd/MMMM/yyyy",  "01" + "/" + [Month Parameter] + "/" + STR(YEAR(TODAY()))))

THEN [Date] END

The formula converts [Month Parameter] to a date and compares this parameter date to the database. It will return database dates equal to parameter date (this month) as well as dates equal to parameter date - 1 (last month). Add [Selected Month] to the filter shelf and exclude Null.

Option 1: Display Results as Dimensions.

Create calculated field [Sales Month]

IF {FIXED : MAX(DATETRUNC('month', [Selected Month]))} = DATETRUNC('month', [Date]) THEN 'This Month Sale'

ELSEIF {FIXED : MIN(DATETRUNC('month', [Selected Month]))} = DATETRUNC('month', [Date]) THEN 'Last Month Sale'

END

This returns string labels which can then be used later to display last month and this month sales.

Option 2: Display Results as Measures.

Create Calculated field [This Month Sale]

IF {FIXED : MAX(DATETRUNC('month', [Selected Month]))} = DATETRUNC('month', [Date]) THEN [Sales] END

This returns actual sale values for this month.

Create Calculated field [Last Month Sale]

IF {FIXED : MIN(DATETRUNC('month', [Selected Month]))} = DATETRUNC('month', [Date]) THEN [Sales] END

This returns actual sale values for last month.

Hope this helps.

Ossai

Hope this helps.

Ossai

4 of 4 people found this helpful
• ###### 2. Re: Show this month and last month sale in column with a parameter selected

Linda,

(1) Create Custom Date with Order Date, select 'month' and date part.

(2) Create parameter Based on this Custom Date. You will get month (1-12). This is a number value which permit you to do mathematical operation. For example, you select parameter=7 which means July, then [Order Date (Months) Parameter]-1 will be June, which is prior month.

(3) Build two calculated fields:

Selected month Sale:

IF DATETRUNC('month',[Order Date])=DATETRUNC('month',DATE(STR([Order Date (Months) Parameter])+'/01/'+STR(Year([Order Date]))))

THEN [Sales] END

Prior Month Sale:

IF DATETRUNC('month',[Order Date])=DATETRUNC('month',DATE(STR([Order Date (Months) Parameter]-1)+'/01/'+STR(Year([Order Date]))))

THEN [Sales] END

(4) Drop Dimensions and Measures to the View as following screen shot:

Hope it helps.

Michael Ye

4 of 4 people found this helpful
• ###### 3. Re: Show this month and last month sale in column with a parameter selected

Okechukwu,

Thanks for your reply. Your reply is very helpful. However, it looks not as simple as Michael Ye's solution, though both of your solutions work for me. Why do you use today() function? The month I selected in the parameter should have no relation with today's year, month or day.

Linda

1 of 1 people found this helpful
• ###### 4. Re: Show this month and last month sale in column with a parameter selected

Michael,

Your solution is very easy to understand, thanks!

Linda

• ###### 5. Re: Show this month and last month sale in column with a parameter selected

You're welcome Linda. I used TODAY() because I assumed 'This Month' to be a month in the current year.

1 of 1 people found this helpful