Forum Discussion
How to combine two different date filters
I have an issue with my date filters. I have a contracts tracking page and I want to show the amount of contracts due within a certain time period and at the same time the dollar amount of that contract within that time frame. I have a contract end date column (PO end date) and a date column that shows when the dollars are to be spent. If I use just one date column or the other in my filter is does not give me the correct info I have to use both of them on seperate filters to see what I want. Is there a way to combine them so I only need one filter?
Create a date table, marked as a date table, and link that to both your date columns in your fact table. Only 1 relationship can be active at once, but you can use USERELATIONSHIP to activate the other relationship when needed.
If you made the link to the PO Date the active one, then your measure for the $ amount would be something like
Dollar Amount = CALCULATE( SUM('Table'[Amount]), USERELATIONSHIP('Date'[Date], 'Table'[Dollar date]) )
3 Replies
johnt75Super User
Create a date table, marked as a date table, and link that to both your date columns in your fact table. Only 1 relationship can be active at once, but you can use USERELATIONSHIP to activate the other relationship when needed.
If you made the link to the PO Date the active one, then your measure for the $ amount would be something like
Dollar Amount = CALCULATE( SUM('Table'[Amount]), USERELATIONSHIP('Date'[Date], 'Table'[Dollar date]) )Thank you!! This worked on every level except now in my table it is also is giving me dollars outside of my filter with no info except the dollar amount. I tried filtering out all the blanks on this one table, but that did not seem to work. I am guessing there is a dax statement I can tack on to my Userelation formula to remove these lines.
- johnt75
It looks like there might be entries which don't have a PO date but do have a dollar amount date.
