Forum Discussion

lesssweet1's avatar
lesssweet1
Regular Visitor
4 years ago
Solved

How to create a variance with a condition

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))

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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 ])

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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 ])

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    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