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.
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))))
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 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |