cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
derekwilson74
Regular 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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors