Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I hope you people are doing well
I am stuck in simple scenario i.e the list of customer having no sale in Current Month but the sale is done last month
e.g In Jan Month the customer have sale but in Feb month there is no sale like I want those customer which is Highlighted in Screenshot
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Solved! Go to Solution.
Hi @mh2587 ,
I created a sample pbix file(see attachment), please check whether that is what you want.
1. Create a date dimension table
2. Create a measure as below to judge whether the customer hasn't current month sales but has the previous month sales
Flag =
VAR _selmonth =
SELECTEDVALUE ( 'Date'[Date].[MonthNo] )
var _selcustomer=
SELECTEDVALUE ( 'Table'[Customer Name])
VAR _cursales =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER ( 'Table', MONTH ( 'Table'[Date] ) = _selmonth )
)
VAR _presales =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Customer Name] = _selcustomer
&& MONTH ( 'Table'[Date] ) = _selmonth - 1
)
)
VAR _minmonth =
MONTH ( CALCULATE ( MIN ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) ) )
VAR _maxmonth =
MONTH ( CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) ) )
RETURN
IF (
_selmonth < _minmonth
|| _selmonth > _maxmonth,
BLANK (),
IF ( ISBLANK ( _cursales ) && NOT ( ISBLANK ( _presales ) ), 1, 0 )
)
3. Create the measures to get the sum of sales base on month name and customer name
Sum of sales =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
'Table',
MONTH ( 'Table'[Date] ) = SELECTEDVALUE ( 'Date'[Date].[MonthNo] )
)
)
Measure = SUMX(VALUES('Date'[Date].[Month]),[Sum of sales])
4. Create a matrix visual as below screenshot and make conditional formatting for Measure
Best Regards
Hi @mh2587 ,
I created a sample pbix file(see attachment), please check whether that is what you want.
1. Create a date dimension table
2. Create a measure as below to judge whether the customer hasn't current month sales but has the previous month sales
Flag =
VAR _selmonth =
SELECTEDVALUE ( 'Date'[Date].[MonthNo] )
var _selcustomer=
SELECTEDVALUE ( 'Table'[Customer Name])
VAR _cursales =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER ( 'Table', MONTH ( 'Table'[Date] ) = _selmonth )
)
VAR _presales =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Customer Name] = _selcustomer
&& MONTH ( 'Table'[Date] ) = _selmonth - 1
)
)
VAR _minmonth =
MONTH ( CALCULATE ( MIN ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) ) )
VAR _maxmonth =
MONTH ( CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) ) )
RETURN
IF (
_selmonth < _minmonth
|| _selmonth > _maxmonth,
BLANK (),
IF ( ISBLANK ( _cursales ) && NOT ( ISBLANK ( _presales ) ), 1, 0 )
)
3. Create the measures to get the sum of sales base on month name and customer name
Sum of sales =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
'Table',
MONTH ( 'Table'[Date] ) = SELECTEDVALUE ( 'Date'[Date].[MonthNo] )
)
)
Measure = SUMX(VALUES('Date'[Date].[Month]),[Sum of sales])
4. Create a matrix visual as below screenshot and make conditional formatting for Measure
Best Regards
Hi Can you help me in this @amitchandak
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
@mh2587 Without knowing your data specificaly, you could do something like:
Measure =
VAR __Today = TODAY()
VAR __Year = YEAR(__Today)
VAR __Month = MONTH(__Today)
VAR __LastMonth = EOMONTH(__Today,-1)
VAR __LMYear = YEAR(__LastMonth)
VAR __LMMonth = MONTH(__LastMonth)
VAR __CurrentCustomers = SELECTCOLUMNS(FILTER('Table',[Month]=__Month && [Year]=__Year),"Customer Name",[Customer Name])
VAR __LastMonthCustomers = SELECTCOLUMNS(FILTER('Table',[Month]=__LMMonth && [Year]=__LMYear),"Customer Name",[Customer Name])
RETURN
CONCATENATEX(EXCEPT(__LastMonthCustomers,__CurrentCustomers),[Customer Name],", ")
Thank you for the response but its not working
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |