cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mwen90
Helper III
Helper III

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. 

Pic 1.PNG

I have then done some if statements  (pic 2)

Pic 2.PNG

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

 

I am not sure where I have gone wrong, 

 

Cheers,

1 ACCEPTED SOLUTION
Nolock
Resident Rockstar
Resident Rockstar

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:

Capture.PNG

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:

Capture2.PNG

View solution in original post

13 REPLIES 13
calculating
Regular Visitor

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
Resident Rockstar
Resident Rockstar

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:

 

Capture.PNG

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

Filtering.PNG

Thank you so much for the help!

Nolock
Resident Rockstar
Resident Rockstar

Hi @mwen90,

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

Hey @Nolock thank you for that, 

 

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

Nolock
Resident Rockstar
Resident Rockstar

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.

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
Resident Rockstar
Resident Rockstar

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.

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
Resident Rockstar
Resident Rockstar

Hi @mwen90,

the mystery is solved 😉

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

Capture.PNG

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

Capture2.PNG

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
Resident Rockstar
Resident Rockstar

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:

Capture.PNG

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:

Capture2.PNG

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors