locked
DAX - use a date to change row contex in a non related table RRS feed

  • Question

  • I'm trying to to implement the following:

    - Two tables, NON related: the first one (TAB_TIME) is a calendar table, the second one (TAB_DIF) is a table where I need to calculate the difference beween a date and the selected date in the TAB_TIME table for each row.

    Substantially, I'd like the following result:

    First example is when no filter is apllied to the TAB_TIME, second is whe only one element in filter is applied

    I Can't define by myself what to write in "Date in TIME" column formula.

    Thanks,

    Davide

    Friday, June 20, 2014 9:05 AM

Answers

  • I Found my solution by usingthis formula with ADDCOLUMN function

    m_00_02:=SUMX(ADDCOLUMNS(Ageing;"DaysF";[DueDate]-[DataFiltraCalc]);IF([DaysF]>=0 && [DaysF]<=2;[Valore];BLANK()))

    where DataFiltraCalc:=IF(HASONEVALUE(DateFiltro[DataFiltro]);VALUES(DateFiltro[DataFiltro]);BLANK())

    Soved..

    • Marked as answer by Davide Becco Friday, July 4, 2014 10:06 AM
    Friday, July 4, 2014 10:06 AM

All replies

  • The image...

    Friday, June 20, 2014 10:30 AM
  • Hi Davide,

    Could you provide an example that helps understanding your scenario (e.g. screenshot of the pivot-table you want to create)?

    Have you read about the concept of disconnected slicers? Even if you do not want to use a slicer to filter down your TAB_TIME table, that concept could help.

    Regards,

    Julian


    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

    Friday, June 20, 2014 7:20 PM
  • Hi Julian,

    I post in a second time two Excel screenshot of what i'd like to obtain, is it enough?

    If not, I'll send you a more detailed view!

    Tuesday, June 24, 2014 8:33 AM
  • I Found my solution by usingthis formula with ADDCOLUMN function

    m_00_02:=SUMX(ADDCOLUMNS(Ageing;"DaysF";[DueDate]-[DataFiltraCalc]);IF([DaysF]>=0 && [DaysF]<=2;[Valore];BLANK()))

    where DataFiltraCalc:=IF(HASONEVALUE(DateFiltro[DataFiltro]);VALUES(DateFiltro[DataFiltro]);BLANK())

    Soved..

    • Marked as answer by Davide Becco Friday, July 4, 2014 10:06 AM
    Friday, July 4, 2014 10:06 AM