Restricted Measures Calculation - Make it Dynamic

[muthuram_shanmugavel2]

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