Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.