Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Retained users from previous month

Hi All,   I have a requirement where i need to calculcate the distinct count of previous month new users placing a order in the current month (Repeated users). i worte the the logic for current mon...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Angelia,

     

    Thanks for your reply. I  did few changed to my logic and it worked fine. I worte Calculcate on top of previous Calculate expression and applied the filter. pls find the logic below

     

    previous logic

    --------------------------------------------------------------------------------------------------------------------------------------------

    AA_PPM_Customers_In_PM =
    VAR PPM_New_Customers_List = CALCULATETABLE(OrderLine,PARALLELPERIOD('date'[FullDate],-2,MONTH),
    OrderLine[Cust_Ord_Rept_In]=0,OrderLine[Ord_Sts_Text]<>"Cancelled",OrderLine[Ord_Sts_Text]<>"Expired")
    RETURN
    CALCULATE(DISTINCTCOUNT(OrderLine[Ord_Cust_Id]),PARALLELPERIOD('date'[FullDate],-1,MONTH),
    FILTER(OrderLine,CONTAINS(PPM_New_Customers_List,OrderLine[Ord_Cust_Id],OrderLine[Ord_Cust_Id])),OrderLine[Ord_Sts_Text]<>"Cancelled",OrderLine[Ord_Sts_Text]<>"Expired")

    ---------------------------------------------------------------------------------------------------------------------------------------------

     

    New Logic

     

    AA_PPM_Customers_In_PM =
    VAR PPM_New_Customers_List = CALCULATETABLE(OrderLine,PARALLELPERIOD('date'[FullDate],-2,MONTH),
    OrderLine[Cust_Ord_Rept_In]=0,OrderLine[Ord_Sts_Text]<>"Cancelled",OrderLine[Ord_Sts_Text]<>"Expired")
    RETURN
    CALCULATE(CALCULATE(DISTINCTCOUNT(OrderLine[Ord_Cust_Id]),
    FILTER(OrderLine,CONTAINS(PPM_New_Customers_List,OrderLine[Ord_Cust_Id],OrderLine[Ord_Cust_Id])),OrderLine[Ord_Sts_Text]<>"Cancelled",OrderLine[Ord_Sts_Text]<>"Expired"),PREVIOUSMONTH('date'[FullDate]))

     

     

    Thanks & Regards,

    Rajeev Bikkani

     

     

     

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

Hi Anonymous,

I think the logic is right, I just wonder why it returns blank, and it's hard to check detailed information without sample table.

>>My report filter is always my selected month.i.e., current month

There is slicer or report level filter including 'date'[FullDate] in your report? Could you please share more details for further analysis.

Best Regards,
Angelia

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Angelia,

 

Thanks for your reply. I  did few changed to my logic and it worked fine. I worte Calculcate on top of previous Calculate expression and applied the filter. pls find the logic below

 

previous logic

--------------------------------------------------------------------------------------------------------------------------------------------

AA_PPM_Customers_In_PM =
VAR PPM_New_Customers_List = CALCULATETABLE(OrderLine,PARALLELPERIOD('date'[FullDate],-2,MONTH),
OrderLine[Cust_Ord_Rept_In]=0,OrderLine[Ord_Sts_Text]<>"Cancelled",OrderLine[Ord_Sts_Text]<>"Expired")
RETURN
CALCULATE(DISTINCTCOUNT(OrderLine[Ord_Cust_Id]),PARALLELPERIOD('date'[FullDate],-1,MONTH),
FILTER(OrderLine,CONTAINS(PPM_New_Customers_List,OrderLine[Ord_Cust_Id],OrderLine[Ord_Cust_Id])),OrderLine[Ord_Sts_Text]<>"Cancelled",OrderLine[Ord_Sts_Text]<>"Expired")

---------------------------------------------------------------------------------------------------------------------------------------------

 

New Logic

 

AA_PPM_Customers_In_PM =
VAR PPM_New_Customers_List = CALCULATETABLE(OrderLine,PARALLELPERIOD('date'[FullDate],-2,MONTH),
OrderLine[Cust_Ord_Rept_In]=0,OrderLine[Ord_Sts_Text]<>"Cancelled",OrderLine[Ord_Sts_Text]<>"Expired")
RETURN
CALCULATE(CALCULATE(DISTINCTCOUNT(OrderLine[Ord_Cust_Id]),
FILTER(OrderLine,CONTAINS(PPM_New_Customers_List,OrderLine[Ord_Cust_Id],OrderLine[Ord_Cust_Id])),OrderLine[Ord_Sts_Text]<>"Cancelled",OrderLine[Ord_Sts_Text]<>"Expired"),PREVIOUSMONTH('date'[FullDate]))

 

 

Thanks & Regards,

Rajeev Bikkani