Using One Date Table to Filter Measures Using Different Date Fields Across A Tabular Model

[conclusnben]

Hello, all,

I am working with a tabular model that has multiple fact tables. One for Charges, one for Encounters, and one for posted Transactions. I have three measures that need to consider three different dates in my model, each measure summing up data from different tables, and each measure using different date fields. They are as follows:

Charge Amt = SUM('Charges'[amt])
Encounters = DISTINCTCOUNT('Patient Encounter'[enc_id])

Transaction Amt = SUM('Transactions'[tran_amt])

Charge Amt must use 'Charges'[closing_date], Encounters must use 'Patient Encounter'[billable_timestamp], and Transaction Amt must use 'Transactions'[closing_date]. 

My question is this: how can I create these measures in a way that allows them to be filtered by a single date (say 'Master Calender'[Date]), and have them populate on visuals next to each other when rolled up to 'Provider Master'[Provider Name] or some other dimensional field that is related to 'Patient Encounter' ?

Any feedback is appreciated! 

[MFelix]

Hi @conclusnben ,

First of all looking at your model you have all your connection with both way filtering this will make you get incorrect results since you are having multiple side filtering from different context.

Regarding your question you just need to create a calendar table (to what I can see you don't have any in your model) make a relationship between that table and the other 3 tables based on the date fields you need to use.

And finally use that calendar table as your slicer and your axis on the visualizations you need, if you are making YTD measures or some other type of calculation that needs to use date filters remember to place the calendar table always and not the date columns from the other 3 tables.