Forum Discussion

Maikg's avatar
Maikg
New Member
3 years ago
Solved

Help Required calculating current month vs January

Hello Everyone

 
Hoping someone can help me to get the right formula in power bi either power query or DAX for the following scenario:
I have an exchange rate table in which I need to calculate the variance of each month vs January.
Below is just an example with RUB currency of what result have to return but the table has more currencies on it.
 
I tried the below formula but is not giving the right result as the table has different currencies on it
 
ABS(CALCULATE(SUM('Month End Spot Rates'[CCY EUR]), FILTER('Month End Spot Rates','Month End Spot Rates'[Month]=1))-'Month End Spot Rates'[CCY EUR])
 

 

  • eliasayyy's avatar
    eliasayyy
    3 years ago

    Thank you for sharing 

     

    January = CALCULATE(
    SUM('Table'[CCY EUR]),
    FILTER(ALLEXCEPT('Table','Table'[Currency]),'Table'[Month] = 1))

     

    Maikg if i solved your uestion please consider accepting as solution adn leaving a kudos behind

     

7 Replies

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

    in order to get only the month of jan for all columns , you need to remove all filters

    in tables, a measur will use row context so in other words filter to the month name , 

    so use calculate[rate],filter(all(table) , [month] = 1)) - [rate of month]

    • Maikg's avatar
      Maikg
      New Member
      3 years ago
      Thanks 
       
      I tried above and below formula but still not getting the result I detailed in the excel as return value
       
      Variance vs Jan = VAR JanuaryRate = CALCULATE(MAX('Month End Spot Rates'[CCY EUR]),FILTER('Month End Spot Rates',MONTH('Month End Spot Rates'[Date])=1)) return ABS('Month End Spot Rates'[CCY EUR])-JanuaryRate