Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I’m struggling in creating a measure and could use your help:
I need a Measure that first checks, if a framework contract (DIM_Frameworkcontract) is active in a given time period.
If the contract is active, I want to sum the invoice amount for that vendor.
I already created the measure and it works, if you look at the data on a monthly perspective.
However, if I want to aggregate the data in a visual on a yearly basis the measure doesn’t work as I expected.
I assume this is due to the filter context in the matrix visual, but I don´t know how to fix it..
Please find attached the measure, screenshots of the data model and the expected output.
Measure
Invoice amount on contract =
VAR selectedDate =
MAX ( DIM_Date[Date] )
RETURN
SUMX (
Fact_Invoices,
VAR StartDatum =
RELATED ( 'DIM_Frameworkcontracts'[Startdate] )
VAR EndDatum =
RELATED ( 'DIM_Frameworkcontracts'[Enddate] )
RETURN
IF (
StartDatum <= selectedDate
&& OR ( EndDatum >= selectedDate, EndDatum = BLANK () ),
Fact_Invoices[Invoice Amount],
0
)
)
Datamodel
DIM_Frameworkcontracts
The contract is valid from 2019-10-01 and the measure „Invoice amount on contract“ should therefore sum up the the invoice amount in the month 2019-10, 2019-11 and 2019-12 for 2019 total.
Output
Solved! Go to Solution.
invoice amount on contract=sumx(DIM_Frameworkcontract,var _s=DIM_Frameworkcontract[startdate] var _e=DIM_Frameworkcontract[enddate] return calculate(sum(Fact_Invoices[Invoice Amount]),keepfilters(datesbetween(dim_date[date],_s,_e))))
invoice amount on contract=sumx(DIM_Frameworkcontract,var _s=DIM_Frameworkcontract[startdate] var _e=DIM_Frameworkcontract[enddate] return calculate(sum(Fact_Invoices[Invoice Amount]),keepfilters(datesbetween(dim_date[date],_s,_e))))
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |