Date Calculator

[mwen90]

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,

[calculating]

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 ) ) }

[Nolock]

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]

@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]

Hi @mwen90,

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

[mwen90]

Hey @Nolock thank you for that, 

I can see the calculations are wrong. Is it something in the formula?

[Nolock]

Hi @mwen90,

have you renamed the column LastServiceDate to "Last Service Date" in your visual or do you have 2 different columns in your table? Have you taken the right column from the right table? Please check it or even better write the calculate column DAX code once again from scratch. I think you calculate DATEDIFF with a wrong column.

[mwen90]

Hey, 

I created a new column and did the datedIF formula for the Last Service Date column.

The column I have added is called 'days'. 

I just redid it and it was the same result, @Nolock 

[Nolock]

Hi @mwen90,

please post your PBIX file to see why it is so. I can fix it then.

As you said in https://community.powerbi.com/t5/Desktop/DATEDIFF-incorrectly-calculating/td-p/368231/page/2 it can really be also your problem.

If you have 2 tables with the relationship 1:N and you try to get a difference of two values, you should always take just one value on both sides.

[mwen90]

There isn't a table connected to it though so I am confused? Thanks so much for assisting, 

I couldn't attach the file but please look at this URL - 

https://drive.google.com/drive/folders/1k6hjkJ-mCKHWjX7KjTLTyyqMHfT0lajA?usp=sharinghttps://drive.go...

Or - https://drive.google.com/drive/folders/1k6hjkJ-mCKHWjX7KjTLTyyqMHfT0lajA 

Hopefully that works. @Nolock thank you!!

[Nolock]

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]

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 · Accepted solution]

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]

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