Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
chefe
Helper II
Helper II

Show same measure value for a specified range

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_NUMBERSTART_DATEEND_DATEVOLUME
Deal 110/6/201711/6/20177000000


Moreover, two date/lookup tables that are connect via relationships

 

 

 

image.png

 

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. 

 

image.png

 

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).

 

image.png

 

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

 

2 ACCEPTED SOLUTIONS
v-qiuyu-msft
Community Support
Community Support

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]
)
)

 

q5.PNG 

 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Ashish_Mathur
Super User
Super User

Hi @chefe,

 

This is my calculated field formula

 

=if(HASONEVALUE('Calendar'[Date]),SUM(DATA[VOLUME]),BLANK())

Download my workbook from here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi @chefe,

 

This is my calculated field formula

 

=if(HASONEVALUE('Calendar'[Date]),SUM(DATA[VOLUME]),BLANK())

Download my workbook from here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-qiuyu-msft
Community Support
Community Support

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]
)
)

 

q5.PNG 

 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Zubair_Muhammad
Community Champion
Community Champion

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]
    )
)

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.