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 there,
I have a data table with one row. A Transaction which has a lifetime of one month, with a value of 7000000.
DEAL_NUMBER | START_DATE | END_DATE | VOLUME |
Deal 1 | 10/6/2017 | 11/6/2017 | 7000000 |
Moreover, two date/lookup tables that are connect via relationships
Putting MONTH and DAY from the lookup table "Maturity dates" on the rows, and a measure SUM(VOLUME) on values section of a pivot table thie following is the result.
So far so good. But what I WANT to do, is modify the mesaure so the same value of 7000000 is shown for all the dates between 10/6 and 11/6.
The below image shows the desired output (sketched by me).
I have tried using calculate but not succeeded. Along the lines of...
NewMeasure:= CALCULATE( [Volume] ;FILTER( ALL(MATURITY_DATES) ;MATURITY_DATES[DATE_KEY]>= MAX(DATA[START_DATE]) ;MATURITY_DATES[DATE_KEY] <= MAX(DATA[END_DATE]) ) )
Do you spot the error in my thinking?
Cheers and kind regards
Solved! Go to Solution.
Hi @chefe,
@Zubair_Muhammad's logic is right, you need to modify it slightly:
Measure =
CALCULATE (
VALUES ( 'DATA'[VOLUME] ),
FILTER (
ALL ( 'DATA' ),
MAX('MATURITY_DATES'[DATE_KEY]) >= 'DATA'[START_DATE]
&& MAX('MATURITY_DATES'[DATE_KEY]) <='DATA'[END_DATE]
)
)
Best Regards,
Qiuyun Yu
Hi @chefe,
@Zubair_Muhammad's logic is right, you need to modify it slightly:
Measure =
CALCULATE (
VALUES ( 'DATA'[VOLUME] ),
FILTER (
ALL ( 'DATA' ),
MAX('MATURITY_DATES'[DATE_KEY]) >= 'DATA'[START_DATE]
&& MAX('MATURITY_DATES'[DATE_KEY]) <='DATA'[END_DATE]
)
)
Best Regards,
Qiuyun Yu
Hi @chefe
Try this
NewMeasure := CALCULATE ( VALUES ( DATA[Volume] ), FILTER ( ALL ( DATA ), MATURITY_DATES[DATE_KEY] >= DATA[START_DATE] && MATURITY_DATES[DATE_KEY] <= DATA[END_DATE] ) )
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 |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |