calculate value between dates and get variance

[topazz11]

Hi,

 I want to calculate the value in Table2 between the StartDate and EndDate in table 2.

After that, I also want to calculate the variance as below.

Could someone help me with this?

Thank you! 

pbix file 

[v-xiaotang · Accepted solution]

Hi @topazz11 

Try this

(1) create the measures

table2_value = CALCULATE(SUM(Table2[vale]),FILTER(ALLSELECTED(Table2),Table2[type]=MIN(Table1[type]) && Table2[date]>=MIN(Table1[start date]) && Table2[date]<= MIN(Table1[finish date])))

variance = MIN(Table1[value])- [table2_value]

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @topazz11 ,

the challenge you are facing has a name - it's called events-in-progress.

This article contains all relevant links: Events-In-Progress | Gerhard Brueckl on BI & Data (gbrueckl.at)

I recommend starting with the article by Jason Thomas.

If you need more guidance, create a pbix file that contains sample data, but still reflects your data model (tables. relationships, calculated columns, and measures). Upload the pbix file to onedrive or dropbox and share the link. If you are using Excel to create the sample data, instead of the manual input method, share the xlsx as well.

Regards,

Tom

[topazz11]

@TomMartens 

here is the sample data. 

pbix file 

I think this measure can calculate the value between the start and finish date...

But, I am not sure how to create variance?? or something better way to do it?

Thanks,

Measure = 
CALCULATE (
    SUM ( Table2[Value] ),
    FILTER (
        Table2,
        Table2[Date] >= MIN( Table1[startdate] )
            && Table2[Date] <= MAX ( Table1[finishdate] )
    )
)