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
deangelodenis
Helper I
Helper I

Calculate the days of a debt (from the past to today) with filters [New Topic]

Hi all, 

 

This is a "continuation" of my last post (http://community.powerbi.com/t5/Desktop/Calculate-the-days-of-a-debt-from-the-past-to-today/m-p/4416...) where with the useful help of different members, we could fix the issue. We worked in a sample file, but when I tried in the original dataset (is for a company), I realized that I have to add some filters (row level) to reach the aim. 

Below is the solution from the last post (thanks @v-jiascu-msft again for everything!), and next to that, is my new require.

 

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]
    )

 

 

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.

 

Captura.PNG

 

 

 

Thank very much in advance!

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @deangelodenis

Try this formula to see if the result meets your needs.

Solution2 =
VAR endDate =
    MAX ( 'Table'[Date] )
RETURN
    SUMX (
        SUMMARIZE (
            FILTER ( ALL ( Debt ), [Flag] = "Yes" && [No valid Inovice] = BLANK () ),
            Debt[Client],
            Debt[Invoice Date],
            "days", DIVIDE (
                SUM ( Debt[$$] ),
                CALCULATE ( SUM ( Debt[$$] ), ALLSELECTED ( Debt ) ),
                1
            )
                * DATEDIFF ( [Invoice Date], endDate, DAY )
        ),
        [days]
    )

Best Regards

Maggie

Hi Maggie, @v-juanli-msft thanks for your time and for your answer.

 

I tried your measure but it didn't work but I think we are close to the solution. Below is a pic with the correct solution to the problem and one of the BI with the new measure. Notice that I need just to use the "yellow" rows because the others I have to filter.

 

 

Captura.PNG

 

 

Captura2.PNG

 

Also, I realized that I forgot creating the relationship between the tables in the BI file. I uploaded it again in the drive (I tried your measure before and after making the relations but didn't work).

 

https://drive.google.com/open?id=1-7ZRt6qVspG1vC3DOzu4Xj2gfuGxEdjm

 

Thanks again!!!

I'm still struggling with this problem.

 

Thanks in advance if someone could help me.

@deangelodenis

 

Hi, i still dont figure out your formula exactly do but try with this :

 

Solution1 =
VAR endDate =
    MAX ( 'Table'[Date] )
RETURN
    SUMX (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( Debt ),
                Debt[Flag] = "Yes"
                    && Debt[No valid Inovice] = BLANK ()
            ),
            Debt[Client],
            Debt[Invoice Date],
            "days", DIVIDE (
                SUM ( Debt[$$] ),
                CALCULATE ( SUM ( Debt[$$] ), ALLSELECTED ( Debt ) ),
                1
            )
                * DATEDIFF ( [Invoice Date], endDate, DAY )
        ),
        [days]
    )

Regards

 

Victor




Lima - Peru

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.