Pass visual-date-filter as parameter for measure based on different date-table

[SG2015]

Hi all

I hope you guys can help me out here…

How can I pass the date-value in the visual-filter as a parameter for the calculation of a time-series measure that is based on a different date table?

I just want to use one visual-filter and not two different ones.

Situation:

My fact-table consists of 14 snapshots of all transactions per end-of-month just like in the screenshot:

DATE_SNAPSHOT	ORDER_ID	DATE_ORDER	ORDER_STATUS	STATUS_DATE	PRICE
31.03.2022	10001	20.03.2022	in progress	20.03.2022	100
30.04.2022	10001	20.03.2022	delivered	02.04.2022	100
31.05.2022	10001	20.03.2022	delivered	02.04.2022	100
30.04.2022	10002	15.04.2022	in progress	15.04.2022	50
31.05.2022	10002	15.04.2022	delivered	02.05.2022	50
…

I.e. Order_ID 10001 comes up 3 times because of 3 different snapshot dates. The snapshots are simply created at the end of each month, there are no dependencies on order dates or status etc.

For filtering a snapshot in my report I use a specific EOM-date-table with relationship to the fact-table.date_snapshot. For calculation of time-series measures I use a different date-table D_DATE_SALES. So when a snapshot-date is selected, how can I create a YTD measure based on that filtered value. As explained I just want to use one visual-filter (snapshot_date)

Relationship is as follows.

Thank you very much in advance. I appreciate any help on this.

Best, Sinan

[NickolajJessen]

I'm not quite able to grasp what you are looking for in your expected output

[SG2015]

Hi NikolajJessen

Thanks for asking.

I want to create a YTD measure for price without using a second visual-date-filter on D_DATE_SALES, but instead use the value from visual-date-filter from D_DATE_EOM.

If I had two visual-date-filters in my report, I easily could create the Price YTD measure like

Price YTD = TOTALYTD(F_SALES[Price],D_DATE_SALES[Date]).

But if I don't want to have the second Date-Filter in my Report, is it possible to somehow reference the value from first filter to calculate Price YTD? I don't know whether this is possible at all - just asking. 

I hope it is clearer now.

Thanks for any hint.

Sinan

[NickolajJessen]

Normally you have a single Calendar Table with dates, in stead of your two dates_EOM and Dates_Sales.
You would then have a relationship between this calendar table and your fact table.

Any futher relationships will be Inactive relationship, which will need to be activede in a DAX measure unsing the function USERELATIONSHIP(Calendar[Date], table[ColumnInInactiverelationship]

[SG2015]

No, I  tried with USERELATIONSHIP-Function. That didn't work. I think the reason is, that 

with my fact-table it is not like the "usual" examples where you have an order-date and shipping date and join same date-table on both dates resulting in one active and inactive relationship. 

As I mentioned before: I have a fact-table in which each record is saved every month again at the end of the month. This results, that there are up to 14 rows of the same record - each with a different eom-date. Therefore I have to have a date-table for just filtering on the snapshot-date and then do all the magic time-series-stuff to calculate the measures on a 2nd date-table. but here behaviour is not as expected. 

I will provide a pbix-test-file, so you can understand the data (simplified) and model. 

[SG2015]

Here is the pbix file with sample data and report

https://drive.google.com/file/d/13Co6ioO_WQrSqX085vZw0hlY7-Yzjy3S/view?usp=sharing