locked
Dax - Related Help RRS feed

  • Question

  • 

    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

     

    Wednesday, April 18, 2018 1:39 PM

Answers

  • Hi Chris,

    Thanks for your response.

    In this scenario, please deleted the relationship between custom holiday table and your fact table.

    Then you can create a measure to count the related days in custom holiday table as below:

    HolidaysNumber := COUNTROWS (
            FILTER (
                holiday,
                holiday[Date] >= MAX ( Sales[Date] )
                    && holiday[Date] <= TODAY ()
            )
        )
    Then you can create a measure called desiredDays minus HolidaysNumber, see below sample DAX query:
    DesiredDays :=
    DATEDIFF ( MAX ( Sales[Date] ), TODAY (), DAY )
        - COUNTROWS (
            FILTER (
                holiday,
                holiday[Date] >= MAX ( Sales[Date] )
                    && holiday[Date] <= TODAY ()
            )
        )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Thursday, April 19, 2018 11:09 AM
    • Marked as answer by jak35 Thursday, April 19, 2018 12:06 PM
    Thursday, April 19, 2018 8:24 AM

All replies

  • Hi Jak35,

    Thanks for your question.

    If you are using Excel 2016, please try to use DAX function DATEDIFF, see below DAX formula:

    DateDiffColumn: =
    DATEDIFF(Fact[TDD Date],TODAY(),DAY)
    

    If you are using Excel 2013 or lower version, please refer to below blog:
    http://www.powerpivot-info.com/post/143-q-how-can-i-calculate-difference-between-two-dates-in-dax-seconds-minutes-hours-days-and-months


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, April 19, 2018 2:40 AM
  • Hi Wilson,

    Thanks for getting back to me.

    I am having to use a custom holiday table with an id because we do not work weekends or bank holidays.  So I need to minus the related.

    Thanks

    Chris

    Thursday, April 19, 2018 7:46 AM
  • Hi Chris,

    Thanks for your response.

    In this scenario, please deleted the relationship between custom holiday table and your fact table.

    Then you can create a measure to count the related days in custom holiday table as below:

    HolidaysNumber := COUNTROWS (
            FILTER (
                holiday,
                holiday[Date] >= MAX ( Sales[Date] )
                    && holiday[Date] <= TODAY ()
            )
        )
    Then you can create a measure called desiredDays minus HolidaysNumber, see below sample DAX query:
    DesiredDays :=
    DATEDIFF ( MAX ( Sales[Date] ), TODAY (), DAY )
        - COUNTROWS (
            FILTER (
                holiday,
                holiday[Date] >= MAX ( Sales[Date] )
                    && holiday[Date] <= TODAY ()
            )
        )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Thursday, April 19, 2018 11:09 AM
    • Marked as answer by jak35 Thursday, April 19, 2018 12:06 PM
    Thursday, April 19, 2018 8:24 AM