Caluculation Quote Between Dates and Consider Product

[fjjohann]

Hello,

My Table contain 3 columns

[Date] [Ativo] [Value]

There are no [Value] per [Ativo] for all [Date]

I need one Mesure:

Show the [Value] according to the [Date] selected in the Dashboard.
If a date set is selected in the dashboard (such as a Month), show the last value.
And when there is no value for the [Ativo] on the selected date, it should show the value of the last existing date.

Logically the measure must take into consideration the [Ativo].

[TomMartens]

And another question, does your model just contain that one table you mentioned or do you also use a separate date table?

[fjjohann]

This is the Table with Values

And i Have a Calendar Table.

The measure must calculate the last [Cot] of each [Ativo] according to the Date selected in the Dashboard.

If it's a Month or Year or All, you should look for the last [Cot].

If there is no [Cot] for the Data and [Asset] selected, you should search for the last one.

[TomMartens]

Hey,

thanks for the sharing a screenshot, maybe you will find some time to provide sample data that can be easily recreated by the people here in the community (downloadable (pbix, xlsx or csv), this will safe me a lot of time during the weekend and prevents issues with insufficient sample data.

Thanks

[fjjohann]

https://drive.google.com/file/d/0Bz1oltp2ew1YajNSdndTd2lia0U/view?usp=sharing

[TomMartens · Accepted solution]

Perfect,

guess this measure will provide what you are looking for

lastvalue = 
CALCULATE(
	LASTNONBLANK(Cotacao[Cot], sum(Cotacao[Cot])
	) 
)

a slight variation will provide the last date

lastdate = 
CALCULATE(
	LASTNONBLANK('Cotacao'[Data], sum(Cotacao[Cot])
	) 
)

Hope this helps

[TomMartens]

Hey,

please prepare sample data and share a link to pbix, excel, csv.