Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi dear Power bi experts,
I have the following Datatabel
Country | Customer | Item | Purchase date |
USA | A | Bicycle | 1-12-2010 |
USA | A | Bicycle | 1-1-2000 |
USA | A | Car | 20-11-2018 |
USA | D | Bicycle | 5-4-2013 |
Netherlands | B | Bicycle | 2-5-2015 |
Netherlands | C | Car | 2-8-2010 |
Nethelands | C | Bicycle | 14-4-2018 |
Now I want the Customers which last bicycle purchase was >5j.
I have done this via a measure lastdate = lastdate([purchases]purchasedate) and put visual level filters on the table
Item = Bicycle
Lastdate = <1-6-2020.
It works and gives me this table and is Exactly what i Want
Customer | Item | Lastdate |
A | Bicycle | 1-12-2010 |
B | Bicycle | 2-5-2015 |
D | Bicycle | 5-4-2013 |
However I do not really like how I have done this because it is not Dynamic because i need to alter the date from time to time. Also i would like to create a count per country which should give me
Country | Count of customers purchase bike >5j |
USA | 2 |
Netherlands | 1 |
I am not able to do that because I keep ending up in with a result were all bikes are counted and not only the last purchashed.
Do you have any good ideas?
Solved! Go to Solution.
Hi @BobKoenen ,
You can create 2 measures as below to achieve it:
Measure =
VAR _tab =
SUMMARIZE (
'Purchases',
'Purchases'[Country],
'Purchases'[Customer],
'Purchases'[Item],
"lastPurchaseDate", MAX ( 'Purchases'[Purchase date] )
)
VAR _lastPdate =
MAXX ( _tab, [lastPurchaseDate] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Purchases'[Customer] ),
FILTER (
'Purchases',
'Purchases'[Country] = MAX ( 'Purchases'[Country] )
&& 'Purchases'[Item] = SELECTEDVALUE ( 'Purchases'[Item], "Bicycle" )
&& DATEDIFF ( _lastPdate, TODAY (), YEAR ) >= 5
)
)
Measure 2 =
SUMX (
VALUES ( 'Purchases'[Country] ),
SUMX ( VALUES ( Purchases[Item] ), [Measure] )
)
Best Regards
Rena
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Yes I do have including current year ofsset. But i only want the date of the last bike and not all bikes purchased before <5j I do not know how to solve this issue using the date table.
Hi @BobKoenen ,
You can create 2 measures as below to achieve it:
Measure =
VAR _tab =
SUMMARIZE (
'Purchases',
'Purchases'[Country],
'Purchases'[Customer],
'Purchases'[Item],
"lastPurchaseDate", MAX ( 'Purchases'[Purchase date] )
)
VAR _lastPdate =
MAXX ( _tab, [lastPurchaseDate] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Purchases'[Customer] ),
FILTER (
'Purchases',
'Purchases'[Country] = MAX ( 'Purchases'[Country] )
&& 'Purchases'[Item] = SELECTEDVALUE ( 'Purchases'[Item], "Bicycle" )
&& DATEDIFF ( _lastPdate, TODAY (), YEAR ) >= 5
)
)
Measure 2 =
SUMX (
VALUES ( 'Purchases'[Country] ),
SUMX ( VALUES ( Purchases[Item] ), [Measure] )
)
Best Regards
Rena
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Yes I have a date table including current year offset but do not know how to set this problem up
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |