Forum Discussion
Pass visual-date-filter as parameter for measure based on different date-table
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
5 Replies
I'm not quite able to grasp what you are looking for in your expected output
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
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]
