cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
derekwilson74 Occasional Visitor
Occasional Visitor

DAX assistance to fill in Gaps with begin and end date

I have a date table and a fact table.  In the fact table I have products, value and an end date.  The end dates all have values.  Any ideas on the DAX to get this to work in a Matrix in Power BI

 

Product  Value    End Date

1              100        9/1/2019

2              125        9/15/2019

3              101        10/12/2019

4              145        10/25/2019

5              135        11/14/2019

6              121        12/31/2019

 

I need to be able to create a matrix report that populates the value from the current month to the end date month.  For example, today is 8/15/19 for row 3 I would see:

 

Product | Aug-19 | Sep-19 | Oct-19 

3                101        101         101

1 REPLY 1
Super User II
Super User II

Re: DAX assistance to fill in Gaps with begin and end date

try this

Measure =
VAR __Today =
    TODAY ()
VAR __EndDate =
    MAX ( 'Table'[End Date] )
RETURN
    IF (
        MAX ( 'Date'[Date] ) >= __Today
            && MIN ( 'Date'[Date] ) <= __EndDate,
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Date', 'Date'[Date] >= __Today && 'Date'[Date] <= __EndDate )
        ),
        BLANK ()
    )

how it looks based on your data

Capture.PNG

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors