Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
irnm8dn
Post Prodigy
Post Prodigy

Days Remaining Calculation

I am looking for some help calculating days remaining.  

 

That is to say that I have a specific field which identifies an "end date", and I am looking to calculate how many days between today and the "end date".  I found similarly asked questions but not of the solutions for those specific concerns were applicable.

 

Would appreciate any help.

 

Thanks in advance.

2 ACCEPTED SOLUTIONS

Awesome.  Actually needed a MIN not MAX, as I was trying to get a day count between today and end date.  If end date is after today then 0.

 

= MIN ( 1 * ( Today ( ) - YourTable[EndDate] ) , 0)

 

Thanks - this was extremely helpful!

View solution in original post

@irnm8dn

 

If [End Date] > Today ( ) Then 0 Else 1 * ( Today ( ) - YourTable[EndDate] ) is equivalent to

 

MAX ( 1 * ( Today ( ) - YourTable[EndDate] ) , 0)

 or

- MIN ( 1 * (  YourTable[EndDate] - Today ( )  ) , 0 )

 

But not to:

MIN ( 1 * ( Today ( ) - YourTable[EndDate] ) , 0)

View solution in original post

11 REPLIES 11

Hi @irnm8dn

 

Do you wish to perform this calculation in a measure or a calculated column ?

You can use DateDiff Function to calculate the number of days between Today and your EndDate.

 

In a calc. Column:

DateDiff ( YourTable[EndDate] , Today() , DAY)

 

In a measure, you'll need to wrap your 1st argument into an aggregator such as Min or Max in this case.

I believe I need it as a column.  Following your instructions I receive an error:

 

Too few arguments were passed to the RELATED funtion.  The minimum argument count for the functions is 1.

 

Not sure what this means.

@irnm8dn

 

Why are you using Related function here ? Haven't mentioned it in my reply.

Sorry.  Corrected by creating New Column.

 

Now I get a meesage that the start date cannot be greater than the end date.  In the case of this data I have both a start date and an end date, and the dataset itself is aggregated by day.

 

Simply meaning that the dataset will have information from the past, present and future.  End dats could be before Today, so I would have expected 0 Days Remaining.  If the if I am between today and the end date, I would expect a day count remaining and lastly if the start date is greater than today, then a count of days between start date and end date.

 

Thanks again for the help.

@irnm8dn

 

Ok try this one which is more universal and covers the cases when your EndDate is superior to Today

 

= 1 * ( Today() - YourTable[EndDate] )

 

You can make the result absolute with Abs( ) if necessary.

 

If it solves your problem, please accept as a solution so everyone can benefit from our discussion !

OK Excel side.  We're getting warmer, and this is awesome.  However, using this solution when the end date is before today, it essentially begins couting days since the end date which ideally for my report would be 0.

 

i.e. if end date > today, then 0

 

Not sure how to incorporate that into your recommendation.

 

Thanks in advance.

@irnm8dn

 

Ok so you can use:

= MAX ( 1 * ( Today ( ) - YourTable[EndDate] ) , 0)

Awesome.  Actually needed a MIN not MAX, as I was trying to get a day count between today and end date.  If end date is after today then 0.

 

= MIN ( 1 * ( Today ( ) - YourTable[EndDate] ) , 0)

 

Thanks - this was extremely helpful!

@irnm8dn

 

If [End Date] > Today ( ) Then 0 Else 1 * ( Today ( ) - YourTable[EndDate] ) is equivalent to

 

MAX ( 1 * ( Today ( ) - YourTable[EndDate] ) , 0)

 or

- MIN ( 1 * (  YourTable[EndDate] - Today ( )  ) , 0 )

 

But not to:

MIN ( 1 * ( Today ( ) - YourTable[EndDate] ) , 0)

@Datatouille

 

Need some additional help...

 

Using the infomation you provided below, I am getting a strange output in my table.  Not sure why the calculation is 6,748?

 

The DAX Statement is Days Remaining = max(1*('XXX Daily Power BI Dataset'[Order end date]-TODAY()),0)

 

Days Remaining.PNG

This is the message when I try your latest recommendation.  Appreciate all the help.

 

The syntax for '[End Date]' is incorrect. (DAX(If [End Date] > Today ( ) Then 0 Else 1 * ( Today ( ) - YourTable[EndDate] ) is equivalent to MAX ( 1 * ( Today ( ) - YourTable[EndDate] ) , 0))).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.