Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Dax - Related - Help

Hi,

 

I have a holiday table which has

 

Date                           Number

01/01/2016                1

02/01/2016                2

 

 etc and in my fact table I have 

 

TDD Date

 

Basically I am wanting a column/measure in my fact table that gets the number for todays date and minus the TDD Number.

 

Any help to go about this appreciated.

 

Thanks

 

 

 

 

Need Help
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    Hi Anonymous,

     

    Did you mean this?

     

    Measure:

    Minus Value =
VAR TDDNum =
    CALCULATE (
        MAX ( Holiday[ID] ),
        FILTER ( Holiday, Holiday[Date] = MAX ( 'Fact'[TDD Date] ) )
    )
VAR TodayNum =
    CALCULATE ( MAX ( Holiday[ID] ), Holiday[Date] = TODAY () )
RETURN
    TDDNum - TodayNum

     

    Thanks,
    Xi Jin.

3 Replies

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Hi Anonymous,

     

    First what are today's number and the TDD number? What's the relationship between holiday table and fact table? And what's the logic to make today's number minus the TDD number?

     

    Please share us more information about your requirement like your table structure and some sample data with its expected result.

     

    Thanks,
    Xi Jin.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Holiday Table

       

      Date                  ID

      01/01/2001        2

      02/01/2001        3

      19/04/2018        500

       

      Fact Table

       

      TDD Date

      01/01/2001

       

      I am joining the two tables on date and I am wanting to get the related ID for tdd date and minus that from todays date ID

       

       

      Thanks

      • v-xjiin-msft's avatar
        v-xjiin-msft
        Icon for Solution Sage rankSolution Sage
        8 years ago

        Hi Anonymous,

         

        Did you mean this?

         

        Measure:

        Minus Value =
VAR TDDNum =
    CALCULATE (
        MAX ( Holiday[ID] ),
        FILTER ( Holiday, Holiday[Date] = MAX ( 'Fact'[TDD Date] ) )
    )
VAR TodayNum =
    CALCULATE ( MAX ( Holiday[ID] ), Holiday[Date] = TODAY () )
RETURN
    TDDNum - TodayNum

         

        Thanks,
        Xi Jin.