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've turned off time intelligence for my report, and am using a Dates Table, and it's marked as a Dates Table.
I want the sum of the Billed Amount (basically sales) for the prior year. This is easy when I don't use a Dates Table - I just add in a PREVIOUSYEAR function.
But with a Dates Table, PREVIOUSYEAR doesn't work for me, and I think it's because I don't have the .[Year] portion of that formula. Here's what I have:
After many hours of experiments, I finally solved it this way:
BILLED AMOUNT, PY =
@Anonymous , I was having the same issue with blanks, and used your formula without the userelationship piece, as my dates are just month end dates, and I am totaling by month. But, the calculation is only summing Feb -Aug and not Jan-Aug. Any idea why that might be?
Thx
Unsure, but it may have something to do with the structure of your dates table and whether it's actually marked as a dates table. I have run into the same issue in the past, but can't recall how I resolved it, though I feel like it had to do with the dates - like maybe I wasn't including the .[Year] portion of the measure.
@Anonymous ...I had an aha moment this morning and checked my date table. I had a typo and it was starting on 2/1 as opposed to 1/1.
Thanks for posting your solution....it really helped me out
@Anonymous , Try like
CALCULATE(CALCULATE(
sum(BillingHistory[BilledAmount]),
USERELATIONSHIP('Dates table'[Date],BillingHistory[QtrEndDate])),
PREVIOUSYEAR('Dates table'[Date]))
QtrEndDate , should not have a timestamp
slicer, filter, and visual should use the period from Dates table. and it should be marked as date table
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-bd52912a5bd4
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |