Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |