Hello
What measure can be used to identify customers with
- No sales in Selected Month
- No sales in Previous Month
- No sales in Previous 3 Month ?
Hi @slyfox,
Please try below measures:
No sales in selected Month = IF ( CALCULATE ( SUM ( F_Invoice[Qty] ), FILTER ( ALL ( F_Invoice ), F_Invoice[LINK_Customer] = SELECTEDVALUE ( D_Customer[LINK_Customer] ) && YEAR ( F_Invoice[LINK_Date] ) = SELECTEDVALUE ( D_Date[DATE_YearNumber] ) && MONTH ( F_Invoice[LINK_Date] ) = SELECTEDVALUE ( D_Date[DATE_MonthNumber] ) ) ) = BLANK (), "No sales", "Have Sales" ) No sales in previous Month = VAR previousYearNumber = IF ( SELECTEDVALUE ( D_Date[DATE_MonthNumber] ) = 1, SELECTEDVALUE ( D_Date[DATE_YearNumber] ) - 1, SELECTEDVALUE ( D_Date[DATE_YearNumber] ) ) VAR previousMonthNumber = IF ( SELECTEDVALUE ( D_Date[DATE_MonthNumber] ) = 1, 12, SELECTEDVALUE ( D_Date[DATE_MonthNumber] ) - 1 ) RETURN IF ( CALCULATE ( SUM ( F_Invoice[Qty] ), FILTER ( ALL ( F_Invoice ), F_Invoice[LINK_Customer] = SELECTEDVALUE ( D_Customer[LINK_Customer] ) && YEAR ( F_Invoice[LINK_Date] ) = previousYearNumber && MONTH ( F_Invoice[LINK_Date] ) = previousMonthNumber ) ) = BLANK (), "No sales", "Have Sales" ) No sales in previous 3 Month = IF ( CALCULATE ( SUM ( F_Invoice[Qty] ), DATESINPERIOD ( D_Date[DATE_Date].[Date], DATE ( SELECTEDVALUE ( D_Date[DATE_YearNumber] ), SELECTEDVALUE ( D_Date[DATE_MonthNumber] ), 1 ), -3, MONTH ), FILTER ( ALL ( F_Invoice ), F_Invoice[LINK_Customer] = SELECTEDVALUE ( D_Customer[LINK_Customer] ) ) ) = BLANK (), "No sales", "Have Sales" )
Best regards,
Yuliana Gu
Dear @v-yulgu-msft
The problem is that the grand total on the column does not display the sum of such customers for which there were no sales.
Could be possible to adjust your measures ?
up
Up
up
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
Join the biggest FREE Business Applications Event in LATAM this February.
User | Count |
---|---|
216 | |
53 | |
49 | |
46 | |
42 |
User | Count |
---|---|
264 | |
211 | |
113 | |
79 | |
65 |