The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
User | Count |
---|---|
16 | |
8 | |
6 | |
6 | |
5 |
User | Count |
---|---|
23 | |
13 | |
13 | |
8 | |
8 |