Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a card visual in my report that states the average time a customer takes to pay per year (when selected in the graph to the right). I wanted to create a measure that does the following : when there is no average in the year 2017, take the average from 2016.
Here is the original measure: Avg Days to Pay = AVERAGE('AR Accounts'[Pay Period (Days)])
And here is the measure I created from it to do what I wanted: Avg Days Pay Final = IF(CALCULATE([Avg Days to Pay], 'Date'[Year]="2017")=BLANK(), CALCULATE([Avg Days to Pay], 'Date'[Year]="2016"), [Avg Days to Pay])
I thought this would work but when I pulled it over to display on the card I got the error below.
Is there another way I can get the result I want with a measure?
Thanks in advance.
As the error message mentioned, we can't compare Integer values with Text values. In this scenario, your 'Date'[Year] column should be Integer, so you can't add quotes on 2017 and 2016 in your filter expression.
Avg Days Pay Final = IF(CALCULATE([Avg Days to Pay], 'Date'[Year]=2017)=BLANK(), CALCULATE([Avg Days to Pay], 'Date'[Year]=2016), [Avg Days to Pay])
Regards,
Thank you! This measure works when I change the Year Format to General instead of Whole Number.
There is still one problem. Here is the measure : Avg Days Pay Final = IF(CALCULATE([Avg Days to Pay], 'Date'[Year]=2017)=BLANK(), CALCULATE([Avg Days to Pay], 'Date'[Year]=2016), [Avg Days to Pay])
It says to only take the 2016 Avg Days to Pay data if the 2017 Avg Days to pay is BLANK, not 0. Yet, when i have avg Days to pay in 2017 that are 0, they are being replaced with the 2016 avg days to pay date. This is not what I want. I want it to show 0 in this case, as it should.
Why is it recognizing BLANK() as 0 too?
Thanks again!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
62 | |
61 | |
52 | |
39 | |
23 |
User | Count |
---|---|
85 | |
58 | |
45 | |
43 | |
38 |