Hi Sam and guys.
I would like your help to solve a problem that I´m facing on my modeling approach
A same “contract” could change its values acording some considerations. So I have an specific data with valid values between an initial date and an end date.
I´d like to build a table where I could bring for any date the valid data in this specific date, so that I could apply all the time inteligence paterns and treat my model as an static one.
What I´ve already done:
I construct a sample model with two date tables and I can select the values filtering in different periods through 2 date slices.once for each table.
Margem = SUMX(Contratos;IF(Contratos[Movimentacao]="Compra"; -(Contratos[P]*Contratos[Q]);Contratos[P]*Contratos[Q])) PnL_Data_Filter_Ini = VAR vData = MAX('Calendar_Ini'[Date]) RETURN CALCULATE([Margem];FILTER(Contratos;Contratos[DfimVig]>=vData && Contratos[DiniVig]<=vData)) PnL_Data_Filter_Fim = VAR vData = MAX('Calendar_Fim'[Date]) RETURN CALCULATE([Margem];FILTER(Contratos;Contratos[DfimVig]>=vData && Contratos[DiniVig]<=vData))
Pnl Delta = [PnL_Data_Filter_Fim]-[PnL_Data_Filter_Ini]
so I can calculate the results for different dates and compare them.
my problem is that as I need use an waterfall chart to explain the changes through time I would like to build an unique table where I can bring the valid data in each date.
So for this I imagine that I have to put in one table the valid values and consider just one date table.
How can I construct a table with this logic ?
Is my approach feasible?
Thanks in advance!