none
Set Active/inactive flag column RRS feed

  • Question

  • Hello!

    I have a date table and fact table in my cube where I have period wise project wise finance data. My project number is of text format since it gets values like (301-19, 301-20). I need to set up a flag (Calculated column) Active/Inactive for project based on the value of one column called Turnover. The condition is if there is a change (+/-) in the Turnover value for a project for last 3 months then the set the project as Active else set it Inactive. How can I write the condition?

     

    Regards

    PV


    Thursday, November 7, 2019 3:00 PM

All replies

  • Hi PV,

    Thanks for posting here.

    Please use some sample table data rather than just abstract expression to support your description, then provide your sample output.

    Such business rule cases could lead us to anywhere, not go to specific destination.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 8, 2019 3:36 AM
  • Hello Will 

    Thank you for your reply. Here is how the data looks like in fact table. 

    

    Expected result:

    Let me now explain. 

    When the project gets loaded into database for the first time then it should get flag as Active. Therefore, project 301-19 get Active status for the month of Jan 2019. Project number in my case is a text column since it has "-" in it.

    The condition to assign Active or Inactive flag to as project is compare current month value_OMS and value_DB with last two month's OMS and DB values. If there is a change in either of the value (OMS or DB) then set the status as active else inactive.

    So for 02.2019, the current month values for OMS and DB are 200 and 60 respectively. If we now compare these values with last 2 months then there is a change in both values. value OMS--> from 100 to 200 and value DB --> from 50 to 60. So the status is active.

    Now if we hop over to 05.2019, then the current month values are same as last two month's values. so the status is changed to Inactive.

    Again in 06.2019, the current month values changes as comapred to last 2 months so the status becomes active again.

    Hope that explains.

    Regards

    PV



    Friday, November 8, 2019 1:13 PM
  • Hi PV,

    Thanks for your detailed description.

    Here are the steps for your reference.

    You need to create calculated columns like this.

    Previous_Value_OMS:=CALCULATE(
               max(fact_Project[Value_OMS]),
    		 FILTER(
    		   FILTER(
    		         FILTER(fact_Project,fact_Project[Project_Number]=EARLIER(fact_Project[Project_Number])),
    				 fact_Project[Year]=EARLIER(fact_Project[Year])
    				 ),
    				 fact_Project[Period]=EARLIER(fact_Project[Period])-1
                   )
    			)
    
    Previous_two_value_OMS:=CALCULATE(
               max(fact_Project[Value_OMS]),
    		 FILTER(
    		   FILTER(
    		         FILTER(fact_Project,fact_Project[Project_Number]=EARLIER(fact_Project[Project_Number])),
    				 fact_Project[Year]=EARLIER(fact_Project[Year])
    				 ),
    				 fact_Project[Period]=EARLIER(fact_Project[Period])-2
                   )
    			)
    
    Previous_Value_DB:=CALCULATE(
               max(fact_Project[Value_DB]),
    		 FILTER(
    		   FILTER(
    		         FILTER(fact_Project,fact_Project[Project_Number]=EARLIER(fact_Project[Project_Number])),
    				 fact_Project[Year]=EARLIER(fact_Project[Year])
    				 ),
    				 fact_Project[Period]=EARLIER(fact_Project[Period])-1
                   )
    			)
    
    Previous_two_value_DB:=CALCULATE(
               max(fact_Project[Value_DB]),
    		 FILTER(
    		   FILTER(
    		         FILTER(fact_Project,fact_Project[Project_Number]=EARLIER(fact_Project[Project_Number])),
    				 fact_Project[Year]=EARLIER(fact_Project[Year])
    				 ),
    				 fact_Project[Period]=EARLIER(fact_Project[Period])-2
                   )
    			)
    
    Active/Inactive:=IF(fact_Project[Value_OMS]=fact_Project[Previous_Value_OMS] && fact_Project[Value_OMS]=fact_Project[Previous_two_value_OMS] && fact_Project[Value_DB]=fact_Project[Previous_Value_DB] && fact_Project[Value_DB]=fact_Project[Previous_two_value_DB],"Inactive","Active")

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 12, 2019 8:18 AM