Comparing a Singular Date to a Date Range

[SamFletch]

Hello all,

I've got a problem that seems fairly simple but I'm struggling to fix myself. 

I have 2 tables, one containing actual data and one containing forecasts.

The actual data operates on a singular date basis (i.e. every day has its own set of data) and the Forecast operates on a monthly basis (i.e. Feb-2021, Mar-2021, which pulls through into the report as 01/02/21 and 01/03/21 respectively). 

In order to tie these together, I've got a date dimension in my report that (in theory) links it all togther, where I've created a "Month Year" ID column to match them up. Every date in February 2021 has the ID 022021, every date in March 2021 has the ID 032021 and so on.

The aim is to compare how many vehicles we have on our fleet to the Forecast. 

I've put some screenshots below with descrptions to show you how it all looks:

Main Sheet:

Forecast Sheet:

Date Dimension:

Relationships:

Report:

Report 2:

As you can see, when using the date from the date dimension, the vehicles on fleet pull through on the actual date but the forecast only pulls through on the first of each month. How do I have it so that the Forecast vehicles see the date and return the value from the "Month Year ID" I've created and linked to the dimension?

Hope this all makes sense.

Thank you for the help.

[amitchandak · Accepted solution]

@SamFletch , refer to this file attached after signature where I have made target available at the day level

Also refer: https://www.youtube.com/watch?v=yPQ9UV37LOU

[SamFletch]

Yeah, that works really nicely, thank you. The "Closingbalance" function is pretty handy, I can already think of a few more uses fo it.