Retained users from previous month

[Anonymous]

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 month and previous month and it works fine..

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

AA_PM_Customers_In_CM =
VAR PM_New_Customers_List = CALCULATETABLE(OrderLine,PREVIOUSMONTH('date'[FullDate]),
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],0,MONTH),
FILTER(OrderLine,CONTAINS(PM_New_Customers_List,OrderLine[Ord_Cust_Id],OrderLine[Ord_Cust_Id])),OrderLine[Ord_Sts_Text]<>"Cancelled",OrderLine[Ord_Sts_Text]<>"Expired") 

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

At the same time i need to calculate the Previous to Previous month new users placing order in previous month. i worte the logic similar to above but im getting  output as Blank which is not correct. My report filter is always my selected month.i.e., current month

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

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")

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

Could somebody helpme to fix the above logic (marked in red)

Thanks & Regads,

Rajeev Bikkani

[v-huizhn-msft]

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 · Accepted solution]

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