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.
Hello everyone,
I'm trying to create a measure to divide quantity value of the current date by the value of the first date that appears forthe month.
(e.g. 31 May Item A quantity value divided by 17 May Item A quantity value)
(e.g. 24 May Item A quantity value divided by 17 May Item A quantity value)
(e.g. 17 May Item A quantity value divided by 17 May Item A quantity value)
Thanks in advance!
Solved! Go to Solution.
@Anonymous,
Try this measure:
Percentage =
VAR vCurrentQty =
SUM ( 'Item'[Quantity] )
VAR vCurrentYear =
YEAR ( MAX ( 'Item'[Date] ) )
VAR vCurrentMonth =
MONTH ( MAX ( 'Item'[Date] ) )
VAR vFirstDateInMonth =
CALCULATE (
MIN ( 'Item'[Date] ),
ALLEXCEPT ( 'Item', 'Item'[Item] ),
YEAR ( 'Item'[Date] ) = vCurrentYear,
MONTH ( 'Item'[Date] ) = vCurrentMonth
)
VAR vEarliestQtyInMonth =
CALCULATE (
SUM ( 'Item'[Quantity] ),
ALLEXCEPT ( 'Item', 'Item'[Item] ),
'Item'[Date] = vFirstDateInMonth
)
VAR vResult =
DIVIDE ( vCurrentQty, vEarliestQtyInMonth )
RETURN
vResult
Sample data:
Proud to be a Super User!
@Anonymous,
Try this measure:
Percentage =
VAR vCurrentQty =
SUM ( 'Item'[Quantity] )
VAR vCurrentYear =
YEAR ( MAX ( 'Item'[Date] ) )
VAR vCurrentMonth =
MONTH ( MAX ( 'Item'[Date] ) )
VAR vFirstDateInMonth =
CALCULATE (
MIN ( 'Item'[Date] ),
ALLEXCEPT ( 'Item', 'Item'[Item] ),
YEAR ( 'Item'[Date] ) = vCurrentYear,
MONTH ( 'Item'[Date] ) = vCurrentMonth
)
VAR vEarliestQtyInMonth =
CALCULATE (
SUM ( 'Item'[Quantity] ),
ALLEXCEPT ( 'Item', 'Item'[Item] ),
'Item'[Date] = vFirstDateInMonth
)
VAR vResult =
DIVIDE ( vCurrentQty, vEarliestQtyInMonth )
RETURN
vResult
Sample data:
Proud to be a Super User!
Thank you for your help kind sir, have a good weekend 😉
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |