Calculate the days of a debt (from the past to today)

[deangelodenis]

Hi all, 

I'm here with a DAX doubt. I need to calculate, for each Client, how many days has their debt. I add a new column ("DAYS.UNTIL"), where I calculated the difference between Today() and the date of the invoice, and to check the client and debt in the present, works great.

The problem is when I use a filter (a slicer, option: "before") and go back in time, my dax still making the same calculation and show me the difference between the invoice date and Today (no the day in the slicer).

Example:

For today (June 09 - as I have another setting, the date format is dd/mm/yy), it works perfectly:

BUT, If I move the slicer to April 30th, I can see the invoice created till that time, but the last column takes the days between Today (June 9) and the invoice date. It has to show, the days between April 30th (or the date that I choose in the slicer) and the invoice date. For the client AA, it has to show 29 in the last column (difference between April 30th  and April 1st) and for BB, it has to show 15).

 

Hope someone could help me and thanks in advance!

[Ashish_Mathur]

Hi,

You should not be writing a calculated column formula.  Write a measure.  But before that, you should have a Calendar Table with a relationship from the Invoice date to the Date column of your Calendar Table.  The slicer should be built from your Calendar Table.  Try this measure

=MAX(Calendar[Date])-MAX(Data[Invoice Date])

Hope this helps.

[deangelodenis]

Hi Ashish, thanks for taking your time and reply me.

Yes, I agree with you. I have to make a measure.

I tried your option, but it doesn't work. Its a pic below (sorry, I'm new in this BI world). It takes just both max (from table date and invoice date), but not change with the row context (neither with the slicer). Also, I don't know how to change the format in a whole number type. 

Thanks again.

[v-jiascu-msft]

Hi @deangelodenis,

Try the formula below. If it doesn't work, please share your file. A dummy one is enough. We need to check the structure.

Solution = datediff(max(Table1[Invoice Date]), max(TablaFechas[Date]) , day)

Best Regards,

Dale

[deangelodenis]

Hi @v-jiascu-msft @Ashish_Mathur @Anonymous ,  I tried the solutions that you gave to me, but anyone works.

Below is the link with the file:

 https://drive.google.com/drive/folders/1-7ZRt6qVspG1vC3DOzu4Xj2gfuGxEdjm?usp=sharing

It's a sample because the original file has too much data, and If we can fix the problem in the sample, I could do the same in the original file.

The goal is:

Show in a new column in the table, the days between the date in the slicer and the invoice date. But, in the original file, I have to show JUST ONE ROW per client, so the "days between" has to be a weighted average. An example below (in the link is the excel file too) :

[v-jiascu-msft]

Hi @deangelodenis,

Try the measure below, please. And change the "Cross Filter direction" to Single.

Measure =
VAR endDate =
    MAX ( 'Table'[Date] )
RETURN
    SUMX (
        SUMMARIZE (
            Debt,
            Debt[Client],
            Debt[Invoice Date],
            "days", DIVIDE (
                SUM ( Debt[$$] ),
                CALCULATE ( SUM ( Debt[$$] ), ALLEXCEPT ( Debt, Debt[Client] ) ),
                1
            )
                * DATEDIFF ( [Invoice Date], endDate, DAY )
        ),
        [days]
    )

Best Regards,

Dale

[deangelodenis]

Hi @v-jiascu-msft, thank for your time and your reply.

I think with are very close to the solution with your post. I tried it and worked, but I found a mistake in the result. We can take just the client AA, and the date May 22th. The solution has to be "13" days, but the measure shows 11 days. Below are BI pic and Excel Image with the correct solution. Maybe I'm wrong with the calculation, please correct me!

[v-jiascu-msft · Accepted solution]

Hi @deangelodenis,

Yes, we need to make a little change indeed. 

Measure =
VAR endDate =
    MAX ( 'Table'[Date] )
RETURN
    SUMX (
        SUMMARIZE (
            Debt,
            Debt[Client],
            Debt[Invoice Date],
            "days", DIVIDE (
                SUM ( Debt[$$] ),
                CALCULATE ( SUM ( Debt[$$] ), ALLSELECTED ( Debt ) ),
                1
            )
                * DATEDIFF ( [Invoice Date], endDate, DAY )
        ),
        [days]
    )

Best Regards,

Dale

[deangelodenis]

@v-jiascu-msft Me again. I have one more thing to add to my issue. 

How do you do the measure, if you don't have to consider some rows from the original dataset? 

For example, in our last file, if I add 2 new columns and I don't have to include in my measure, the non-empty rows in the column "No valid invoice" and the rows with "No" (in the column "Flag"). How should I apply a filter in the measure to avoid them?

Objective: Eliminate for the measure of the days of the debt, the rows with "x" in the column "No valid invoice" or with "No" in the column "Flag"

Below is the link with the file (is the "Sample2" doc): https://drive.google.com/open?id=1-7ZRt6qVspG1vC3DOzu4Xj2gfuGxEdjm

And the picture below has to show the same result as the last solution.

[v-jiascu-msft]

Hi @deangelodenis,

I would suggest you open a new thread in this forum. One topic a thread.

Best Regards,

Dale

[deangelodenis]

@v-jiascu-msft perfect. 

This is the new post:

http://community.powerbi.com/t5/Desktop/Calculate-the-days-of-a-debt-from-the-past-to-today-with-fil...

Thanks!

[deangelodenis]

@v-jiascu-msft THANK YOU VERY MUCH! works perfectly!

I'm going to try to do the same in the main dataset. Hope I could resolve too.

Thanks again for your time and for helping me with this issue.

[Anonymous]

@v-jiascu-msft's solution above also seems to work for me.  But if I understand what you are trying to do, the other option like my mention above.  (Updated with your Sample file)

DateAllSelectedMeasure = CALCULATE(MAX('Table'[date]),ALLSELECTED(Debt))

Invoice_Date_Measure = MAXX(KEEPFILTERS(VALUES(Debt[Invoice Date])),CALCULATE(MAX(Debt[Invoice Date])))

DifferenceMeasure = DATEDIFF([Invoice_Date_Measure],[DateAllSelectedMeasure], DAY)

[deangelodenis]

Hi @Anonymous, thanks again for your time.

Your answer works! Using that measures I get the days between the slicer and the invoice date. Excellent!

I still having troubles with show one row per client, and the weight average of the days of the debt. With the @v-jiascu-msft post we are closer, but still showing a wrong number.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Your second MAX may need to be a MAXX so that you can keepfilters for the invoice number

something like this 
=MAX(Calendar[Date])-MAXX(KEEPFILTERS(VALUES('Data'[Invoice_Number])), CALCULATE(MAX('Data'[Date])))

[deangelodenis]

Hi @Anonymous

Thank you too for your time to reply.

I tried your suggestion but doesn't work:

[Anonymous]

Ahh I see, so to get your max date, ensure that you are using a Measure and the calculation should be 

DateAllSelectedMeasure = CALCULATE(MAX('Date'[office_calendar_date]),ALLSELECTED(Invoice))

then you can use that date in your calculation for time difference note here you will need to continue to use Measures as Columns a column will not update when you change a slicer

So you will then need 2 more Measures 

Invoice_Date_Measure = 
MAXX(
	KEEPFILTERS(VALUES(Invoice[Item_Date])),
	CALCULATE(MAX(Invoice[Item_Date]))
)

DifferenceMeasure = DATEDIFF([Invoice_Date_Measure],[DateAllSelectedMeasure], HOUR)