Skip to Content

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.

temp.PNG

So Expected sample report is,


temp.PNG

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.

temp.PNG


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.

temp.PNG


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.

          temp.PNG

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

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

     temp.PNG

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

 

     temp.PNG

      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.

        

     temp.PNG

     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.

          temp.PNG

   

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.

     temp.PNG

          temp.PNG

Best Regards,

Muthuram

3 Comments
You must be Logged on to comment or reply to a post.
  • Hi Muthuram,

    Thanks for providing wonderful document with scenario.

    I am facing one issue while doing this scenario.

    As per your example, I have created the Calculated column “Month”. After this, i am creating restricted Column (CURRENT_MONTH_SALES) and i am trying to use Calculated Column “Month”. But it is not appearing here. Please find the Snap for your reference.

    Doubt : Can we use calculated Column in Restricted Column.

    Thanks.

     

  • Hi,

    I got the solution. I didnt select Calculated colunm(“Month”) in Projection View. Due to this, I am unable to use it in restricted. After enabling, I can use calculated Column in Restricted column of Aggregation.

    Thanks.

  • 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,

    VaraPrasad.