Help Required calculating current month vs January

[Maikg]

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]

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]

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

[eliasayyy]

and you need to use all inside the filter 

Variance vs Jan = VAR JanuaryRate = CALCULATE(MAX('Month End Spot Rates'[CCY EUR]),FILTER(ALL('Month End Spot Rates'),MONTH('Month End Spot Rates'[Date])=1)) return ABS('Month End Spot Rates'[CCY EUR]-JanuaryRate)

[eliasayyy]

can you please hsrae sample dataset @Maikg 

[Maikg]

Items.Year	Date	Currency	Month	CCY EUR
2023	01/01/2023	AED	1	0.25106
2023	01/01/2023	AMD	1	0.00233
2023	01/01/2023	RUB	1	0.01311
2023	01/01/2023	ALL	1	0.00858
2023	01/02/2023	RUB	2	0.01258
2023	01/02/2023	ALL	2	0.00869
2023	01/02/2023	AED	2	0.25688
2023	01/02/2023	AMD	2	0.00243
2023	01/03/2023	RUB	3	0.01187
2023	01/03/2023	AMD	3	0.00235
2023	01/03/2023	ALL	3	0.00882
2023	01/03/2023	AED	3	0.24970
2023	01/04/2023	AMD	4	0.00233
2023	01/04/2023	ALL	4	0.00901
2023	01/04/2023	AED	4	0.24710
2023	01/04/2023	RUB	4	0.01114
2023	01/05/2023	AMD	5	0.00243
2023	01/05/2023	RUB	5	0.01153
2023	01/05/2023	AED	5	0.25484
2023	01/05/2023	ALL	5	0.00909
2023	01/06/2023	ALL	6	0.00938
2023	01/06/2023	AMD	6	0.00238
2023	01/06/2023	AED	6	0.25061
2023	01/06/2023	RUB	6	0.01046
2023	01/07/2023	AED	7	0.24722
2023	01/07/2023	ALL	7	0.00945
2023	01/07/2023	AMD	7	0.00235
2023	01/07/2023	RUB	7	0.00984

[eliasayyy · Accepted solution]

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

[Maikg]

Thanks, yes this resolved part of the issue, I was able to use another formula to calculate the variance between months and January