So I'm coming over from Power BI, and I am used to using dax to create measures.
My dilemma is, I have 2 different KPI's that use 2 different dates to calculate. I am wondering if I can use a disconnected date table to filter them both.
In Power BI, I could create an inactive relationship between the date column in my calendar table and the 2 dates in my fact table. Then I could use a Calculate function with the UseRelationship function to get this result.
What I was thinking is something like SUM(IF [FactDate1] >= MIN([Cal].[Date]) AND [FactDate1] <= MAX([Cal].[Date]) THEN [DollarValue] ELSE 0 END) and repeat with a [FactDate2].
However, it doesn't seem like I can compare a table value with an aggregate function.
Any thoughts on this would be helpful.
Thank you!
Zachary,
ZZ is correct, we really need a mock-up of your data in a packaged workbook to provide any ideas for your solution.
Also, perhaps you can provide a little more information as to why the Calendar table needs to be disconnected from your Fact table?
Thanks,
Kaz.
