Forum Discussion
Filtering by dates in two columns
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
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?
- SergioSilvaPT4 years agoResolver V
Yes, as you can see in the photo of the model you should connect then all with the date column, but it's important to keep the relationship direction.
Tell me if you need further help.