cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamFletch
Frequent Visitor

Comparing a Singular Date to a Date Range

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:

SamFletch_3-1612866859263.png

 

 

Forecast Sheet:

SamFletch_1-1612866736892.png

 

Date Dimension:

SamFletch_2-1612866768362.png

 

Relationships:

SamFletch_7-1612867039741.png

 

Report:

SamFletch_4-1612866911350.png

 

Report 2:

SamFletch_6-1612866956323.png

 

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.

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors