Forum Discussion
Filtering by dates in two columns
I have a set of data that looks like the below table. As you can see it has two date columns, the settlement (when it was bought) and the maturity (when it was sold). I would like to be able to ...
Anonymous i think i understand what you need, but for this you will have to use a Calendar table.
And you will have to create a copy of your data table, like the model in example:
You have a Calendar Table and then Calendar A and Calendar B that are reference Calendar, you have go to modeling and click New Table and the you create:
Calendar A = 'Calendar'
Calendar B = 'Calendar'
Table (A) = 'Table (B)'Table (A) is your actual data table.
On Table (A) create the measure:
Cost A = CALCULATE(
SUM('Table (A)'[Cost]),
KEEPFILTERS('Calendar A'[Date]),
ALL('Calendar'[Month])
)
On Table (B) create the measure:
Cost B = CALCULATE(
SUM('Table (B)'[Cost]),
KEEPFILTERS('Calendar B'),
ALL('Calendar'[Month])
)
Then you can recreate the connections to model as shown above.
You will be able to achieve what you want:
Regards,
Sérgio Silva
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Unfortunately I'm not able to recreate what you've done.
is there supposed to be a [Date] after 'Calendar B' in the below code?
Cost B = CALCULATE( SUM('Table (B)'[Cost]), KEEPFILTERS('Calendar B'), ALL('Calendar'[Month]) )- what are the relationships betwen calendar A & Calendar, and Calendar B & Calendar? are you linking date to date in both of these?