Showing values for months with no record

[LaurenceSD]

Hi,

I've got a table that takes a snapshot of the database every month and records certain values, with this report, bookings if they are too old, drop off, and stop being updated to save space. So when I plot those values on a matrix I get the below - is there a way I can get the final value for each booking to show for future months, even though there is no record for that date - so taking the top line 252514 that stopped being updated in April 2019, but what I'd like to show is that value of £3561 for April 2019 onwards. I've tried an all formula but can't get it to work. At the moment I've not got a date table as I'm only using one table for all my data. One thing to note is I only want the value carried forwards, not backwards (i.e. if a new booking gets added I don't want to see that value showing for prior months only future months). I've tried a PQ solution and I've got a column with only the most recent month value in it, but I can't get it to display for all dates

Hopefully, this makes sense and any help is greatly appreciated

[v-yingjl · Accepted solution]

Hi @LaurenceSD ,

You need to create a date table like this and create relationshiop bewteen the two tables based on the date column:

Date table = DISTINCT('Table'[Date])

Create a measute like this:

A = 
VAR _sum =
    SUM ( 'Table'[Value] )
RETURN
    IF (
        ISBLANK ( _sum ),
        CALCULATE (
            MAX ( 'Table'[Value] ),
            ALLEXCEPT ( 'Date table', 'Date table'[Date].[Year] )
        ),
        _sum
    )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LaurenceSD]

Hi,

We've got around to working on this again and we've got a couple of issues with the formula, wondering if @v-yingjl you're able to assist or anybody else?

2 Problems are:

1. The value that is being returned is the highest value ( MAX ( 'Table'[Value] )), but this isn't what we need, what we need is the most recent value.

2. The formula is also moving the values backwards, whereas we only want to push the value forwards. For example, if we had a record for 1st Feb 2019, 1st March 2019 and 1st April 2019 we would want the value from 1st April 2019 to show for 1st May 2019 and onwards but not for 1st Jan 2019 and beforehand.

Hopefully, this makes sense and any suggestions are appreciated.