Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |