Dynamic Date Filter

[Anonymous]

Hello,

I aim to get a table looking like this:

Explanation:

The needed Information is stored in 2 tables "projecttasks" and "times".

The estamated time is recorded for each projecttask in table "projecttasks". The actual time is recorded in table "times" with a date and a reference to the specific projecttask. Remaining time is not recorded anywhere and needs to be calculated.

This is what i already got:

As you can see, the remaining time is calculated correctly. What i need is a date filter to filter the actual time. e.g. i just want to see the actual time in September and October. Later times should be ignored. Correlating the remaining time should be filtered likewise whereas the estimated time should not be affected.

For now, when i filter the date as described above, the estimated time is not affected (correct), the actual time gets filtered (correct) and the remaining time is not filtered (wrong). Thererfore, i get "wrong" calculations:

In the above picture i filtered the date and as you can see the actual time is filtered. Now the calculation for remaining time is incorrect (e.g. TP00 4,00 - 11,50 should be - 7,50, shows -22,50) as here all actual times are used and the filter does not affect the column.

Right now "Remaining Time" is a calculated column stored in projecttasks and is calculated like this:

Remaining Time = projecttasks[Estimated Time] - SUMX(times; IF(times[Key] = projecttasks[Key]; times[Actual Time]))

Can anyone help me with my problem here?

Regards,

Dennis

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Please try to create a measure as below. If it doesn't meet your requirement, kindly share your pbix to me.

Remaining Time = SUM(projecttasks[Estimated Time]) - SUMX(filter(times,times[Key] = projecttasks[Key]) ,times[Actual Time]))

Regards,

Frank

[Anonymous]

Hi @v-frfei-msft,

Thanks for your help. Your formula is working great.

Regards,

Dennis

[sdjensen]

Hi @Anonymous,

You main problem is that Remaining is calculated as a column - this column is calculated before you set you period filter, hence it will always return the same no matter what period you select. You will have to calculate Remaining as a measure if it should be affected by the period filter on your actual.

[Anonymous]

Hello @sdjensen,

Thanks for your answer. I haven't worked with measures yet, so could you give me an example of how i could do the measure as the measure needs data from 2 different tables?