Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a Matrix table with coulmns from my DIM Account table and columns from my Fact table. I want to add multiple measures to the matrix. My measures work as expected, but one of them doesn't.
I have a measure time intelligence measure that is shows me the sales from last year. I want the measure to ignore all other columns in the matrix except for the Customer Name. I currently have this measure which works when I remove the columns from the matrix, but appears as blank when the columns are added. Any ideas?
Total Bookings - One Year Ago = CALCULATE([Amount Booked YTD] , DATEADD('DIM Date'[Date], -1, YEAR), ALLEXCEPT('DIM Account', 'DIM Account'[CustomerName], 'DIM Account'[CompanyParent]))
Solved! Go to Solution.
I manged to get it to work by adding the Customer ID column from the fact table to the Matrix and using the following code .
CALCULATE([Amount Booked YTD],DATEADD('DIM Date'[Date], -1, YEAR), ALLEXCEPT('Deals', 'Deals'[CompanyID], 'DIM Date'[Date], 'DIM Account'[CompanyParent], 'DIM Account'[CustomerName]))
I manged to get it to work by adding the Customer ID column from the fact table to the Matrix and using the following code .
CALCULATE([Amount Booked YTD],DATEADD('DIM Date'[Date], -1, YEAR), ALLEXCEPT('Deals', 'Deals'[CompanyID], 'DIM Date'[Date], 'DIM Account'[CompanyParent], 'DIM Account'[CustomerName]))
@PoweredOut , Ideally, last year should work with
CALCULATE([Amount Booked YTD] , DATEADD('DIM Date'[Date], -1, YEAR))
Make sure date table has all dates and is marked as date table
or try like
Total Bookings - One Year Ago = CALCULATE(CALCULATE([Amount Booked YTD] ALLEXCEPT('DIM Account', 'DIM Account'[CustomerName], 'DIM Account'[CompanyParent])), DATEADD('DIM Date'[Date], -1, YEAR))
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
108 | |
90 | |
61 |
User | Count |
---|---|
171 | |
138 | |
132 | |
102 | |
86 |