Forum Discussion
Table with two dates field
Hi there.
I think I've solved your problem. In the file with the solution, please note that there is bi-filtering enabled between 2 tables, Actions and Action Dates. This is not necessary and should be removed in a full solution but I've put it in there to filter the Action Dates in a slicer for easier navigation. But in a production model, it should be removed and not relied upon unless you know what you are doing, why you are doing it and what the consequences of this are. Otherwise, please stay away from bi-directional filtering as it can be dangerous.
When scrutinizing the solution, please pay attention to the data in the tables. Both date tables are marked as a Date table in the solution to be able to use time-intel functions against them.
Here's an outline of how it works. If you don't select any date from Action Date, then you compare [Total Actions] against [Total Actions PY] in the usual way. When you filter by dates in Action Date, then not only [Creation Dates] are shifted back one year for comparison but also Action Dates. This I believe is how you wanted it.
https://1drv.ms/u/s!ApyQEauTSLtOgYM98Uqy2CW0t8Whmg?e=3tjIAg
Best
D
Hello Anonymous ,
You can make use of a calendar table with dual relationships(one active and another inactive):
Then you can use measures USERELATIONSHIP with CALCULATE:
Orders by Order Date = SUM(dtOrders[Order Quantity])
Orders by Ship Date =
CALCULATE (
dtOrders[Orders by Order Date],
USERELATIONSHIP ( dtOrders[Ship Date], dtCalendar[Date] )
)
Orders (SD - PY) =
CALCULATE (
dtOrders[Orders by Ship Date],
SAMEPERIODLASTYEAR ( dtCalendar[Date] )
)
Then you can use these measures in your visuals:
Sample solution file here
Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
Connect on LinkedIn
- Hi vivran22.
One calendar in the solution will not achieve what's required. You have to have 2 of them out of necessity.
Best
D