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 if statements  (pic 2)

But if I look at the table, my data is still wrong as it has data from this month. (pic 3)

 

I am not sure where I have gone wrong, 

 

Cheers,

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:

13 Replies

  • Nolock's avatar
    Nolock
    Resident Rockstar
    7 years ago

    Hi mwen90,

    you have created two new calculated columns but you don't still have a filter on them - at least it seems so.

    To achieve that apply a filter in the Filters Pane in Power BI Desktop like:

     

    • mwen90's avatar
      mwen90
      Helper III
      7 years ago

      Nolock thank you. I have been able to apply this, but I am still seeing records which aren't +90 days

      Thank you so much for the help!

      • Nolock's avatar
        Nolock
        Resident Rockstar
        7 years ago

        Hi mwen90,

        just for debugging purpose: Please add the column Days to your table to see what values are there.

  • calculating's avatar
    calculating
    Frequent Visitor
    4 years ago

    With the date calculator code below, you can calculate your two date ranges as you want.
    EVALUATE
    VAR StartDate = DATE ( 2022, 01, 01 )
    VAR EndDate = DATE ( 2022, 05, 31 )
    RETURN
    { ( "Year", DATEDIFF ( StartDate, EndDate, YEAR ) ),
    ( "Quarter", DATEDIFF ( StartDate, EndDate, QUARTER ) ),
    ( "Month", DATEDIFF ( StartDate, EndDate, MONTH ) ),
    ( "Week", DATEDIFF ( StartDate, EndDate, WEEK ) ),
    ( "Day", DATEDIFF ( StartDate, EndDate, DAY ) ) }