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.
have folloiwng data set
Week No | Region | invoice number | Invoice Type | Bucket | Balance Amount |
Wk1 | UK | INV001 | Overdue | [0-30] | 100 |
Wk1 | CE | INV002 | Overdue | [46-60] | 100 |
Wk1 | UK | INV003 | Overdue | [31-45] | 100 |
Wk1 | UK | INV004 | Overdue | [46-60] | 100 |
Wk1 | NA | INV005 | Overdue | [0-30] | 100 |
Wk1 | CE | INV006 | Overdue | [0-30] | 100 |
Wk1 | CE | INV007 | Overdue | [46-60] | 100 |
Wk1 | NA | INV008 | Overdue | [31-45] | 100 |
Wk1 | CE | INV009 | Overdue | [0-30] | 100 |
Wk1 | CE | INV010 | Overdue | [46-60] | 100 |
Wk2 | UK | INV004 | Overdue | [31-45] | 100 |
Wk2 | CE | INV005 | Overdue | [0-30] | 100 |
Wk2 | UK | INV006 | Overdue | [46-60] | 100 |
Wk2 | UK | INV007 | Overdue | [31-45] | 100 |
Wk2 | NA | INV008 | Overdue | [46-60] | 100 |
Wk2 | CE | INV009 | Overdue | [0-30] | 100 |
Wk2 | CE | INV010 | Overdue | [0-30] | 100 |
Wk2 | NA | INV011 | Overdue | [46-60] | 100 |
Wk2 | CE | INV012 | Overdue | [31-45] | 100 |
I want to calculate Sum of balance amount for invoices which were there in Wk1 and are not in Wk2 (for Ex in above table sum of INV1, INV2 & INV3). Also want sum of newly created invoices in Wk2 such as INV11 & INV12.
I should be able to put it in matrix which will have region in rows or bucket.
[Inv Amt In WK1\WK2] =
var In_ = "Wk1"
var NotIn_ = "Wk2"
var InvoicesOfInterest =
EXCEPT(
CALCULATETABLE(
DISTINCT( T[Invoice Number] ),
T[Week No] = In_
),
CALCULATETABLE(
DISTINCT( T[Invoice Number] ),
T[Week No] = NotIn_,
ALL( T )
)
)
return
CALCULATE(
SUM( T[Balance Amount] ),
InvoicesOfInterest
)
// The second measure
[Inv Amt in WK2\WK1] =
var In_ = "Wk2"
var NotIn_ = "Wk1"
var InvoicesOfInterest =
EXCEPT(
CALCULATETABLE(
DISTINCT( T[Invoice Number] ),
T[Week No] = In_
),
CALCULATETABLE(
DISTINCT( T[Invoice Number] ),
T[Week No] = NotIn_,
ALL( T )
)
)
return
CALCULATE(
SUM( T[Balance Amount] ),
InvoicesOfInterest
)
Tried creating folloiwng dax but it is not working
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 |
---|---|
47 | |
24 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |