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
VAR __Today =
VAR __EndDate =
MAX ( 'Table'[End Date] )
MAX ( 'Date'[Date] ) >= __Today
&& MIN ( 'Date'[Date] ) <= __EndDate,
SUM ( 'Table'[Value] ),
FILTER ( 'Date', 'Date'[Date] >= __Today && 'Date'[Date] <= __EndDate )
how it looks based on your data
Proud to be a Datanaut!
Join us in the third Triple A event!
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
Make sure you didn't miss any of the things that happened in the community in January!