Forum Discussion

mwen90's avatar
mwen90
Helper III
7 years ago
Solved

Date Calculator

Hey,    I am trying to do a report to show me any items whereby the entered date is +90 days.    I have created a column to calculate the data date to today's date.  I have then done some ...
Need Help
  • Nolock's avatar
    Nolock
    7 years ago

    Hi mwen90,

    the DAX function DATEDIFF definition can be found here: https://docs.microsoft.com/en-us/dax/datediff-function-dax It says, the first param is StartDate and the second is EndDate. I think you should change the order in your Days expression.

    Days = DATEDIFF(PlantEuipment_PlantEquipment[LastServiceDate];Today();DAY)

    Then change the data type of LastServiceDay to date/time:

    And I've also extend your DaysGroup logic to:

    DaysGroup =
IF (
    PlantEuipment_PlantEquipment[Days] < 0;
    BLANK ();
    IF (
        PlantEuipment_PlantEquipment[Days] <= 80;
        "Within service date";
        IF (
            PlantEuipment_PlantEquipment[Days] <= 90;
            "Approaching service date";
            IF ( PlantEuipment_PlantEquipment[Days] > 90; "Service Required" )
        )
    )
)

    The result:

Nolock's avatar
Nolock
Resident Rockstar
7 years ago

Hi mwen90,

the mystery is solved ;)

The problem was, that the column Days was summarized, what you don't want.

Change it to Don't summarize and it starts working:

  • mwen90's avatar
    mwen90
    Helper III
    7 years ago

    Sorry I tried that and I had the same result as in your picture, there are still dates in the table from July? That's only 15 days not within the 90 day period specified! 

     

    This is so confusing, thanks so much for the assitance! Nolock 

  • Nolock's avatar
    Nolock
    Resident Rockstar
    7 years ago

    Hi mwen90,

    the DAX function DATEDIFF definition can be found here: https://docs.microsoft.com/en-us/dax/datediff-function-dax It says, the first param is StartDate and the second is EndDate. I think you should change the order in your Days expression.

    Days = DATEDIFF(PlantEuipment_PlantEquipment[LastServiceDate];Today();DAY)

    Then change the data type of LastServiceDay to date/time:

    And I've also extend your DaysGroup logic to:

    DaysGroup =
IF (
    PlantEuipment_PlantEquipment[Days] < 0;
    BLANK ();
    IF (
        PlantEuipment_PlantEquipment[Days] <= 80;
        "Within service date";
        IF (
            PlantEuipment_PlantEquipment[Days] <= 90;
            "Approaching service date";
            IF ( PlantEuipment_PlantEquipment[Days] > 90; "Service Required" )
        )
    )
)

    The result:

  • mwen90's avatar
    mwen90
    Helper III
    7 years ago

    You are a data god Nolock , thanks for the help!