cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LaurenceSD
Helper II
Helper II

Showing values for months with no record

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

 

Screenshot 2021-06-15 121340.png

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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
    )

matrix.png

 

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.

View solution in original post

1 REPLY 1
v-yingjl
Community Support
Community Support

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
    )

matrix.png

 

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.

View solution in original post

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

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors