How to create a variance with a condition

[lesssweet1]

Hi there,

I am have some roadblcks when I creating new measure, my purpose is create a new metric like a some dots to show the variance in percentage.

I have created the Variance, and wondering know how can I create the Variance% based on the Enquiry FY condition, should we use the DAX with Filter or And ? 

I tried to write some dax but it can;t work,

Many thanks,

Chelsea 

Variance = CALCULATE(COUNT('Cleansed data'[File No]),FILTER('Cleansed data','Cleansed data'[Enquiry FY]=2022))-CALCULATE(COUNT('Cleansed data'[File No]),FILTER('Cleansed data','Cleansed data'[Enquiry FY]=2021))

[v-henryk-mstf]

Hi @lesssweet1 ,

Whether the advice given by @amitchandak  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[amitchandak · Accepted solution]

@lesssweet1 , if you have date table try to use time intelligence this year vs last year

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

with Date of only with year level table

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])