Matrix - Dates on both axis showing % based on running totals

[pledington]

Hi, I want to repicate a table I have easily created in excel into Power BI and tried to use a Matrix to show the below table from excel but it isn't working for me:

This was the excel formula: =IFERROR(SUMIFS(Data!$D:$D;Data!$B:$B;"<="&E$3;Data!$A:$A;$B5)/SUMIFS(Data!$C:$C;Data!$A:$A;$B5);"")

I created a table with the blow data from the excel file:

I then created some quick measures like this:

Offer Price running total in Estimated Close Date =
CALCULATE(
 SUM('Data'[Offer Price]);
 FILTER(
  ALLSELECTED('Data'[Estimated Close Date]);
  ISONORAFTER('Data'[Estimated Close Date]; MAX('Data'[Estimated Close Date]); DESC)
 )
)

Trying to create a measure for the running total for both Invoice Value and Offer Price I then created another measure called conversion rate that divided the 2 but get the following Matrix:

The only column that is correct is the Total on the right, any help with this is appreciated.

[v-ljerr-msft · Accepted solution]

Hi @pledington,

Based on my test, you should be able to follow steps below to create the Matrix in Power BI.

1. Add a individual Date table which contains the corresponding Month-Year data in your mode.

2. Use the formulas below to create measures to calculate the Offer Price running total in Estimated Close Date.

Total Invoice = 
VAR invoiceDate =
    CALCULATE ( MAX ( 'Date'[Month Year] ), ALLSELECTED ( 'Date'[Month Year] ) )
VAR closeDate =
    CALCULATE (
        MAX ( 'Date'[Month Year] ),
        ALLEXCEPT ( 'Date', 'Date'[Month Year] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            YEAR ( Table1[Invoice Date] ) <= YEAR ( invoiceDate )
                && MONTH ( Table1[Invoice Date] ) <= MONTH ( invoiceDate )&&NOT(ISBLANK(Table1[Invoice Date]))&&YEAR ( Table1[Estimated Close Date] ) = YEAR ( closeDate )
                && MONTH ( Table1[Estimated Close Date] ) = MONTH ( closeDate )
        )
    )

Total Offer = 
VAR closeDate =
    CALCULATE (
        MAX ( 'Date'[Month Year] ),
        ALLEXCEPT ( 'Date', 'Date'[Month Year] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            YEAR ( Table1[Estimated Close Date] ) = YEAR ( closeDate )
                && MONTH ( Table1[Estimated Close Date] ) = MONTH ( closeDate )
        )
    )

Offer Price running total in Estimated Close Date = DIVIDE([Total Invoice],[Total Offer])

3. Then you should be able to show Date[Month Year] column as Rows and also as Columns, and [Offer Price running total in Estimated Close Date] as Values on the Matrix to get your expected result.

Here is sample pbix file for your reference. 

Regards

[CheenuSing]

Hi @pledington

Can you please share some data and the business logic to build the final output expected to help fina solution.

Put the data in one drive and share the link.

Cheers

CheenuSing

[pledington]

Hi @CheenuSing thanks for your reply.

I have attached the sample data from the excel file: Sample data

Part of our business is tracking the conversion/renewal of sales, where the Offer price represents last year's sale and Invoice value is this year's sale and compares when the sale was due to be made and when it actually happened.

I currently produce a report that includes the table example I included from excel so that we can see as at each month end how sales are tracking and the lag of sales across time. For example if you look at Jun-17 from the top dates I can see that by the end of the month we had only closed 40% of the sales that were due for that month but we had already closed 67% of next month sales. Equally I can also see that by the end of Jul-17 we had closed 60% of sales due in Jun-17 and 80% by the end of Aug-17, meaning we re-gained 20% of Jun's due sales in both months. and as of right now we closed 80% of those Jun sales.

This is helpful as we like to know how each individual monthly sales (were the original sale was due, dates on the left) are tracking and as we track along each month end (dates along the top) we can see the lag effect on sales and here each month ended up. I do it as a table as bar and line graphs end up too busy.

[v-ljerr-msft · Accepted solution]

Hi @pledington,

Based on my test, you should be able to follow steps below to create the Matrix in Power BI.

1. Add a individual Date table which contains the corresponding Month-Year data in your mode.

2. Use the formulas below to create measures to calculate the Offer Price running total in Estimated Close Date.

Total Invoice = 
VAR invoiceDate =
    CALCULATE ( MAX ( 'Date'[Month Year] ), ALLSELECTED ( 'Date'[Month Year] ) )
VAR closeDate =
    CALCULATE (
        MAX ( 'Date'[Month Year] ),
        ALLEXCEPT ( 'Date', 'Date'[Month Year] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            YEAR ( Table1[Invoice Date] ) <= YEAR ( invoiceDate )
                && MONTH ( Table1[Invoice Date] ) <= MONTH ( invoiceDate )&&NOT(ISBLANK(Table1[Invoice Date]))&&YEAR ( Table1[Estimated Close Date] ) = YEAR ( closeDate )
                && MONTH ( Table1[Estimated Close Date] ) = MONTH ( closeDate )
        )
    )

Total Offer = 
VAR closeDate =
    CALCULATE (
        MAX ( 'Date'[Month Year] ),
        ALLEXCEPT ( 'Date', 'Date'[Month Year] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            YEAR ( Table1[Estimated Close Date] ) = YEAR ( closeDate )
                && MONTH ( Table1[Estimated Close Date] ) = MONTH ( closeDate )
        )
    )

Offer Price running total in Estimated Close Date = DIVIDE([Total Invoice],[Total Offer])

3. Then you should be able to show Date[Month Year] column as Rows and also as Columns, and [Offer Price running total in Estimated Close Date] as Values on the Matrix to get your expected result.

Here is sample pbix file for your reference. 

Regards

[pledington · Accepted solution]

Thanks @v-ljerr-msft that was very helpful. I tried this in my live data and only had to change it a little, the reason being I added a slicer uising the [Month Year] that can span a 4 year period but was stricted to a 2 year period and also wanted it based on cumulative $ values so the measures ended up looking something like this:

Total Offer = 
VAR closeDate =
    CALCULATE (
        MAX ( 'Date'[Month Year] );
        ALLEXCEPT ( 'Date'; 'Date'[Month Year] )
    )
RETURN
    CALCULATE (
        SUM ( 'Table1'[Expected Revenue] );
        FILTER (
            'Table1';
            YEAR ( 'Table1'[Estimated Close Date] ) = YEAR ( closeDate )
                && MONTH ( 'Table1'[Estimated Close Date] ) = MONTH ( closeDate )
        )
    )

Total Invoice = 
VAR invoiceDate =
    CALCULATE ( MAX ( 'Date'[Month Year] ); ALLSELECTED ( 'Date'[Month Year] ) )
VAR closeDate =
    CALCULATE (
        MAX ( 'Date'[Month Year] );
        ALLEXCEPT ( 'Date'; 'Date'[Month Year] )
    )
RETURN
    CALCULATE (
        SUM ( 'Table1'[Invoice Value]);
        FILTER (
            'Table1'; 'Table1'[Invoice Date] <= invoiceDate
                &&NOT(ISBLANK('Table1'[Invoice Date]))&&YEAR ( 'Table1'[Estimated Close Date] ) = YEAR ( closeDate )
                && MONTH ( 'Table1'[Estimated Close Date] ) = MONTH ( closeDate )
        )
    )