Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculate Totals per Keyword

Hi everyone,

Please, can someone help me with the next challenge in Power BI?

I’ve got the next table:
afbeelding

and I want to calculate the amount per keyword:

 

afbeelding
afbeelding.png1093x643 40.9 KB

 

The challenge is to calculate all the amounts after a certain date to the following date with the next keyword.

Hopefully you understand what I mean by looking at the 2 examples.

When calculating in Power BI I’ve got the next report and this is not what I want :wink: :

afbeelding

Thanks in advance,

180608 Test Enterprise DNA - Amount per Keyword.xlsx 1 (12.2 KB)

180608 BI2-89 Total Amount per Keyword.pbix 2 (102.6 KB)

Need Help

11 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    HI Anonymous

     

    Try this MEASURE

     

    Measure =
VAR mydate =
    MAX ( 'Table Invoices'[Invoicedate] )
VAR nextnonblankdate =
    CALCULATE (
        MIN ( 'Table Invoices'[Invoicedate] ),
        FILTER (
            ALLEXCEPT ( 'Table Invoices', 'Table Invoices'[Customernumber] ),
            'Table Invoices'[Invoicedate] > mydate
                && NOT ( ISBLANK ( 'Table Invoices'[Keyword] ) )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( SELECTEDVALUE ( 'Table Invoices'[Keyword] ) ), BLANK (),
        ISBLANK ( nextnonblankdate ), SUM ( 'Table Invoices'[Amount excl VAT] ),
        CALCULATE (
            SUM ( 'Table Invoices'[Amount excl VAT] ),
            FILTER (
                ALLEXCEPT ( 'Table Invoices', 'Table Invoices'[Customernumber] ),
                'Table Invoices'[Invoicedate] >= mydate
                    && 'Table Invoices'[Invoicedate] < nextnonblankdate
            )
        )
    )