I have three tables:
- Transaction Amounts
- Transaction Calendar
- Unrelated Table
What I need to do, is filter the transaction calendar using a filter based on the value sin the unrelated table, so that the transaction amounts are only shown in the selected period.
Table1:
Transaction Date ID | Transaction Amount
Table 2:
Transaction Date ID | Transaction Date | PeriodID
Table 3:
PeriodID | Period Value
The date in Table 2 can ONLY have one PeriodID.
E.g. I have 3 different Periods in Table 3. 1, 2 and 3.
If I select Period Value 1, I only want to see the dates that have PeriodID 1.
If I select Period Value 2, I only want to see the dates that have PeriodID 1 AND 2.
If I select Period Value 3, I only want to see the dates that have PeriodID 1, 2 AND 3.
I've tried various SWITCH formulas, but somehow I cannot get them to work.
Transaction by Ye:=
VAR Selection = SELECTEDVALUE(FilterPeriodYear[PeriodYearID])
RETURN
CALCULATE([Transactions] ,
FILTER(_TransactionDateCalendar, _TransactionDateCalendar[PeriodByYear] in FILTER(ALL(FilterPeriodYear), FilterPeriodYear[PeriodYearID] <= Selection)))
Transactions by Y:=
VAR Selection = SELECTEDVALUE(FilterPeriodYear[PeriodYearID])
RETURN
CALCULATE([Transactions] ,
SWITCH( TRUE() ,
Selection = 1 , FILTER(_TransactionDateCalendar, _TransactionDateCalendar[PeriodByYear] = 1)
, Selection = 2 , FILTER(_TransactionDateCalendar , _TransactionDateCalendar[PeriodByYear] in {(1), (2)})
, Selection = 3 , FILTER(_TransactionDateCalendar , _TransactionDateCalendar[PeriodByYear] in {(1), (2), (3)})))
Transacions by Year:=
CALCULATE([Transactions], SWITCH(SELECTEDVALUE(FilterPeriodYear[PeriodYearID]) ,
1 , FILTER(_TransactionDateCalendar , _TransactionDateCalendar[PeriodByYear] = 1) ,
2 , FILTER(_TransactionDateCalendar , _TransactionDateCalendar[PeriodByYear] in {(1), (2)}),
3 , FILTER(_TransactionDateCalendar , _TransactionDateCalendar[PeriodByYear] in {(1), (2), (3)}),
4 , FILTER(_TransactionDateCalendar , _TransactionDateCalendar[PeriodByYear] in{(1), (2), (3), (4)}),
5 , FILTER(_TransactionDateCalendar, _TransactionDateCalendar[PeriodByYear] in{(1), (2), (3), (4), (5)})
)
)
I hope someone can help 🙂
