Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So, I've 2 tables as under
SALES table:
ID | SALE_DATE |
1 | 09-21-2021 |
2 | 09-21-2021 |
3 | 09-21-2021 |
2 | 09-21-2021 |
3 | 09-21-2021 |
1 | 09-21-2021 |
5 | 07-22-2021 |
6 | 09-21-2021 |
9 | 09-21-2021 |
7 | 08-21-2021 |
8 | 05-21-2021 |
CUSTOMER Table
ID |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
I want to create 2 measures:
1st would be the count of customers that have no sales in the last 2 months, so in this case it would be 2 (8 and 10)
and second measure would give the list of all those customer ID's (8 and 10)
Right now I use this measure to get the list of all ID's that have no sales in last 2 months
show_hide = VAR current_name = MIN(SALES[ID])
VAR chk_not_in = IF( COUNTROWS( FILTER( ALL(SALES), SALES[ID]= current_name && SALES[SALE_DATE])> DATE(YEAR(NOW()),MONTH(NOW())-2, DAY(NOW())) ) )= 0, 0, 1 )
VAR chk_in = IF( COUNTROWS( FILTER( ALL(CUSTOMER), CUSTOMER[ID] = current_name ) ) = 0, 0, 1 )
RETURN IF(chk_in = 1 && chk_not_in = 1, 1, 0)
So every ID with a show_hide of "0" would be the ones that dont have any sales in the last 2 months I was wondering if there's an easy way to do it and also, I'm not sure how to get the count of all those ID's
Solved! Go to Solution.
(since you were asking referencing sales in the last 2 months (I take it for this current year) I have changed your dates in the sales table to 2020 (and not 2021 as you had in your "sales table")
Here is one way of doing this:
The model:
You can then use the following measures.
To count the customers with no sales in the last 2 months:
Count Customers no sales in Last 2 months =
VAR ThisYear = YEAR(TODAY())
VAR ThisMOnth = MONTH(TODAY()) -2
VAR _today = DAY(TODAY())
VAR YMD = ThisYear *10000 + ThisMOnth *100 + _today
VAR _Year = YEAR(MAX(SalesTable[Sales Date])) * 10000
VAR _Month = MONTH(MAX(SalesTable[Sales Date])) * 100
VAR _DAY = DAY(MAX(SalesTable[Sales Date]))
VAR SalesDateNUm = _Year + _Month + _DAY
VAR Customers = VALUES(CustomerTable[ID])
VAR CustomersL2Months = CALCULATETABLE(VALUES(CustomerTable[ID]),
FILTER(SalesTable,
SalesDateNUm > YMD))
RETURN
COUNTROWS(EXCEPT(Customers, CustomersL2Months))
To list the customers with no sales in the last 2 months:
List Customers no sales in last 2 months =
CONCATENATEX(FILTER(CustomerTable,
[Count Customers no sales in Last 2 months] >0),
CustomerTable[ID], ", ")
Proud to be a Super User!
Paul on Linkedin.
(since you were asking referencing sales in the last 2 months (I take it for this current year) I have changed your dates in the sales table to 2020 (and not 2021 as you had in your "sales table")
Here is one way of doing this:
The model:
You can then use the following measures.
To count the customers with no sales in the last 2 months:
Count Customers no sales in Last 2 months =
VAR ThisYear = YEAR(TODAY())
VAR ThisMOnth = MONTH(TODAY()) -2
VAR _today = DAY(TODAY())
VAR YMD = ThisYear *10000 + ThisMOnth *100 + _today
VAR _Year = YEAR(MAX(SalesTable[Sales Date])) * 10000
VAR _Month = MONTH(MAX(SalesTable[Sales Date])) * 100
VAR _DAY = DAY(MAX(SalesTable[Sales Date]))
VAR SalesDateNUm = _Year + _Month + _DAY
VAR Customers = VALUES(CustomerTable[ID])
VAR CustomersL2Months = CALCULATETABLE(VALUES(CustomerTable[ID]),
FILTER(SalesTable,
SalesDateNUm > YMD))
RETURN
COUNTROWS(EXCEPT(Customers, CustomersL2Months))
To list the customers with no sales in the last 2 months:
List Customers no sales in last 2 months =
CONCATENATEX(FILTER(CustomerTable,
[Count Customers no sales in Last 2 months] >0),
CustomerTable[ID], ", ")
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |