Forum Discussion

SG2015's avatar
SG2015
Frequent Visitor
4 years ago

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 tabl...
Need Help
Show and Tell
SG2015's avatar
SG2015
Frequent Visitor
4 years ago

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's avatar
NickolajJessen
Icon for Solution Sage rankSolution Sage
4 years ago

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's avatar
    SG2015
    Frequent Visitor
    4 years ago

    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.