I have a table that contains asset information such as Item Number, Fleet Value (Asset * Qty), purchase date and sell/disposal date. I am looking for some help in creating a visual to show sum(fleet value) by month. Do you guys have any ideas on how to relate an aggregate fleet value between months of being purchased and sold?
Solved! Go to Solution.
Hi @burtta ,
I created some data:
Here are the steps you can follow:
1. Select [purchase date] and [sell/disposal date] -- Transform – Unpivot Columns.
Result:
2. Create calaculated table.
Date =
CALENDAR(
DATE(
2021,1,1),
DATE(
2021,12,31))
3. Create measure.
Measure =
var _Qty=
COUNTX(
FILTER(ALL('Table'),
'Table'[Attribute]=MAX('Table'[Attribute])&&
YEAR('Table'[Value])=YEAR(MAX('Date'[Date]))&&
MONTH('Table'[Value])=MONTH(MAX('Date'[Date]))),[Item number])
var _sum=
SUMX(
FILTER(ALL('Table'),
'Table'[Attribute]=MAX('Table'[Attribute])&&
YEAR('Table'[Value])=YEAR(MAX('Date'[Date]))&&
MONTH('Table'[Value])=MONTH(MAX('Date'[Date]))),[Asset])
return
_sum * _Qty
4. Result:
If I have misunderstood your meaning, please provide your desired output and your pbix without privacy information.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Here is the measure that resolved my problem.
Here is the measure that resolved my problem.
Hi,
Share some sample data and show the expected result.
Hi @burtta ,
I created some data:
Here are the steps you can follow:
1. Select [purchase date] and [sell/disposal date] -- Transform – Unpivot Columns.
Result:
2. Create calaculated table.
Date =
CALENDAR(
DATE(
2021,1,1),
DATE(
2021,12,31))
3. Create measure.
Measure =
var _Qty=
COUNTX(
FILTER(ALL('Table'),
'Table'[Attribute]=MAX('Table'[Attribute])&&
YEAR('Table'[Value])=YEAR(MAX('Date'[Date]))&&
MONTH('Table'[Value])=MONTH(MAX('Date'[Date]))),[Item number])
var _sum=
SUMX(
FILTER(ALL('Table'),
'Table'[Attribute]=MAX('Table'[Attribute])&&
YEAR('Table'[Value])=YEAR(MAX('Date'[Date]))&&
MONTH('Table'[Value])=MONTH(MAX('Date'[Date]))),[Asset])
return
_sum * _Qty
4. Result:
If I have misunderstood your meaning, please provide your desired output and your pbix without privacy information.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
User | Count |
---|---|
103 | |
83 | |
68 | |
47 | |
47 |
User | Count |
---|---|
155 | |
91 | |
82 | |
69 | |
67 |