Two Date Ranges, Two Columns

[herraclitus]

Hi Champs,

I'm creating a report where I want to pick two dates, say A and B, and then show Expenses (+) and Invoices (-) per project up to those two dates and between them.

The project's "Starting Balance" is the total of all expenses (Hours and Costs) and Invoices before date A.

The project's "Current" columns show the total Expenses and invoices between dates A and B.

The project's "End Balance" is the sum of the above; everything up to date B.

The facts are:

1) Hours worked and Fee per hour, I multiply them to get the Hours fees (+).

2) Costs (+).

3) Invoices (-).

My question is: how can I build measures and dashboard filters to get the Start Balance and End Balance for any two given dates?

Do I need multiple filters? Do I need to duplicate my data or restructure my model?

Or can I use one date filter and do the rest with DAX?

For example:

If I pick the dates (1st Jan 2021) and (31st March 2021), the Start Balance for each project is all the Expenses and Invoices up to Dec 2020 summed. And the End Balance = Start Balance + all Expenses and Invoices in 2021 Q1. Basically just another Start Balance but as of 31st March 2021 instead of 1st Jan 2021.

With Gratitude ❤️

[herraclitus]

Solved by duplicating the dataset and having two date tables for the two ranges.

[lbendlin]

Your data model seems to be missing the Calendar table?

[herraclitus]

I have added a calendar table based on the earliest and latest hours booked, I also connected it to all my tables that have dates subject to calculations.

Do I need another calendar table because I'm dealing with two date ranges?

Or do I use two filters with the same calendar table?