Forum Discussion
Calculation based on 2 or more variables (slicer/parameter?)
- Anonymous1 year ago
Hi Harom
Thank you for reaching out microsoft fabric community forum.
could you please provide some sample data.so that we can look into it more effienciently.
Thank you.
Harom Hey,
try below things
1st table
CurrencySelection =
DATATABLE("Currency", STRING, {
{"USD"},
{"EUR"},
{"GBP"}
})
2nd table
PeriodSelection =
DATATABLE("Period", STRING, {
{"MTD"},
{"YTD"},
{"QTD"},
{"Full Year"},
{"PY MTD"},
{"PY YTD"}
})
once you have above 2 disconnected (Newly created table) then you have create below measure
SelectedSales =
VAR SelectedCurrency = SELECTEDVALUE(CurrencySelection[Currency])
VAR SelectedPeriod = SELECTEDVALUE(PeriodSelection[Period])
-- Step 1: Apply currency conversion logic (example rate logic)
VAR SalesInCurrency =
SWITCH(
SelectedCurrency,
"USD", [Sales_USD],
"EUR", [Sales_EUR],
"GBP", [Sales_GBP],
[Sales_USD] -- default
)
-- Step 2: we have to write a period logic now
RETURN
SWITCH(
SelectedPeriod,
"MTD", CALCULATE(SalesInCurrency, DATESMTD(DateTable[Date])),
"YTD", CALCULATE(SalesInCurrency, DATESYTD(DateTable[Date])),
"QTD", CALCULATE(SalesInCurrency, DATESQTD(DateTable[Date])),
"Full Year", CALCULATE(SalesInCurrency, YEAR(DateTable[Date]) = YEAR(TODAY())),
"PY MTD", CALCULATE(SalesInCurrency, DATESMTD(SAMEPERIODLASTYEAR(DateTable[Date]))),
"PY YTD", CALCULATE(SalesInCurrency, DATESYTD(SAMEPERIODLASTYEAR(DateTable[Date]))),
SalesInCurrency -- default
)
above solution is fully scaleble for future.
Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
HarishKM
I've got the Table and selected value part working. When I want to apply this in for example the VAR SalesinCurrency the way you described (I've added a New Column with the calculation), there is no result in the column.
-- Step 1: Apply currency conversion logic (example rate logic)
VAR SalesInCurrency =
SWITCH(
SelectedCurrency,
"USD", [Sales_USD],
"EUR", [Sales_EUR],
"GBP", [Sales_GBP],
[Sales_USD] -- default
)
My logic: