Forum Discussion
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 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
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
2 Replies
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,
AngeliaHi 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
