cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Function days360 with current date

I would like to create a column which counts the days from today till a certain date.

And devide the margin by the number of days to create a certain ranking.

 

 

I will upload a table which contains company, margin and EFD.

Column must be shown as follows (today is 10 jan 2018) :

 

Company        Margin         Estimated first delivery (EFD)     |     days till EFD         Ranking

Company A      10000            11-Feb-2018                                  |     31                        322,5806

Company B      15000            13-jan-2018                                   |     3                         5000

etc...

 

Can someone help me creating this within power BI?

If any further questions please let me know.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Interkoubess Established Member
Established Member

Re: Function days360 with current date

Hi @Anonymous,

 

Please create a calculated column with the formula below I created a column with today value Tdate:

 

=DATEVALUE([Estimated first delivery (EFD)])-DATEVALUE([Tdate])

Let us know if it does not work.

 

For your second request yes an if can resolve it.

 

Ninter

Anonymous
Not applicable

Re: Function days360 with current date

Thank you @Interkoubess

It is working the way I wanted. Just had to switch the column from date -> whole number in modeling.

 

6 REPLIES 6
Interkoubess Established Member
Established Member

Re: Function days360 with current date

Hi @Anonymous,

 

Please use datediff to compute the days till EFD ( create a column) and then for ranking use Marging/Days till EFD.

 

let us know if it does not work.

 

Ninter.

 

Anonymous
Not applicable

Re: Function days360 with current date

Hi @Interkoubess

 

Thank you very much for your reply.

When I try to do this it shows the error: In DATEDIFF function, the start date cannot be greater than the end date.

 

I think this derives from the fact that some dates in the EFD column are in the past. This is because this colomn is manually filled by employees and sometimes they forget to update this.

 

The dates in the past should go either to -x or 0

Is there a way to still make this work with past dates as well?

 

Interkoubess Established Member
Established Member

Re: Function days360 with current date

Hi @Anonymous,

 

Could you please give sample with the data that are not working, I will make a try and let you know.

 

Ninter.

Anonymous
Not applicable

Re: Function days360 with current date

@Interkoubess

 

The same as the data above. But than with EFD in the past

 

Company        Margin         Estimated first delivery (EFD)     |     days till EFD         Ranking

Company A      10000            11-Feb-2018                                  |     31                        322,5806

Company B      15000            13-jan-2018                                   |     3                          5000

Company C      12000            12-Dec-2017                                  |     -29                       -413,793

Company D        9000            02-Jan-2018                                  |     -8                         -1125

 

In stead of heaving a - ranking I might even prefer to have a 0 valeu. But I assume this can be easaly done by IF(

Interkoubess Established Member
Established Member

Re: Function days360 with current date

Hi @Anonymous,

 

Please create a calculated column with the formula below I created a column with today value Tdate:

 

=DATEVALUE([Estimated first delivery (EFD)])-DATEVALUE([Tdate])

Let us know if it does not work.

 

For your second request yes an if can resolve it.

 

Ninter

Anonymous
Not applicable

Re: Function days360 with current date

Thank you @Interkoubess

It is working the way I wanted. Just had to switch the column from date -> whole number in modeling.

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 258 members 2,825 guests
Please welcome our newest community members: