Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

 

 

View solution in original post

2 REPLIES 2
v-huizhn-msft
Employee
Employee

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
Not applicable

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

 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.