# Restricted Measures Calculation – Make it Dynamic

This is my sample sales data.

Sales_amount by Product and Sold_on date.

Requirement is Report has to be created CURRENT_MONTH_SALES, PREVIOUS_MONTH_SALES by PRODUCT wise.

So Expected sample report is,

This can be achievable by creating Restricted Measures. But Limitations are there.

Limitations:

If we create Restricted Measures – CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES withstatic values as condition,

Models has to be changed for every month.

Else If we create Restricted Measures – CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES with Input Parameters as condition,

User has to enter Current_Month and Previous_Month Value whenever he check the report.

How To Make Restricted Measures Calculation Dynamic?

So Restricted Measures – CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES has to be calculated dynamically based on report execution date.

How to make CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES value to be Dynamic?

This is the Steps I have followed to achieve this:

1. I created Calculation view with default node as Aggregation node. Then I included Projection node and connected it to Aggregation node.

I added the “SALES_DATA” table into Projection node.

2. In Projection node, I have created one Calculated Column – “MONTH”

“SOLD_ON” is DATE field. From this field, Month is calculated.

3. In Aggregation node, I have created two Restricted Measures – CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES

In this Restricted Measures, Conditions will be dynamic and It is written in Expression editor.

RM: CURRENT_MONTH_SALES:

Restriction Condition: (“MONTH” = Int(midstr (string(now()), 6, 2)))

Current_Month calculated using “midstr” and “now()” functions.

Then Restriction condition is applied on “MONTH” Column.

RM: CURRENT_MONTH_SALES:

Restriction Condition: (“MONTH” = Int(midstr (string(now()), 6, 2))-1)

Previous_Month calculated using “midstr” and “now()” functions and Substrcted with “1”.

Then Restriction condition is applied on “MONTH” Column.

4. Finally I created the report – CURRENT_MONTH_SALES, PREVIOUS_MONTH_SALES by PRODUCT wise.

CURRENT_MONTH_SALES, PREVIOUS_MONTH_SALES would be calculated dynamically based on report execution date.

No need to change the model for every month.

Best Regards,

Muthuram

### Assigned Tags

1 Comment
You must be Logged on to comment or reply to a post.

Hi Muthuram,

Thanks for the nice document. But the logic is failing for the below scenarios. Can you explain me how can we eliminate the issue.

Example: I had taken same scenario with the below example data.

I implemented the same logic for PREVIOUS_MONTH_SALES as for my sample data. But the logic failing to get the previous month data.

I have used the same expression as you used for getting the previous month data

Int(midstr(string(now()),6,2)-1) it's giving 0 instead 12

its calculating as 1-1=0.

Regards,