Function days360 with current date

[Anonymous]

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.

[Interkoubess · Accepted solution]

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

[Interkoubess]

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]

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]

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]

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

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

Thank you @Interkoubess

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