Power BI - Compare Measures over Period

Question

I'm currently working on a PowerBI script, which gives me headaches. I introduced a measurement, which shows the amount of sales for every company in a corporation. Which basically looks like this.

MarketShare = 
DIVIDE(
  CALCULATE(
    SUM('Monthly Reports'[Active Certificates])
  ), 
  CALCULATE(
    SUM('Monthly Reports'[Active Certificates]),
    ALL('Monthly Reports'[Institute])
  )
)

The output of this is a beautiful matrix showing me the marketshare of every company compared to the total sales.

Now I should display in a matrix below, (or the same, even better) the change of market share for every company every month. Hence, July should display a blank cell since it has no data before. August should display in the first line +0,34 in September +0,3 ... etcetera for every entry in line one and continuing down. I tried to implement a measure, which deducts always the values from the previous one, but I have not been successful so far.

Market Share Prev Period = 
(
  DIVIDE(
    CALCULATE(
        SUM('Monthly Reports'[Active Certificates])
    ), 
    CALCULATE(
        SUM('Monthly Reports'[Active Certificates]),
        ALL('Monthly Reports'[Institute])
    )
  )
)
-
(
DIVIDE(
    CALCULATE(
        SUM('Monthly Reports'[Active Certificates]),
        DATEADD('Monthly Reports'[LoadDate], -1, MONTH)
    ), 
    CALCULATE(
        SUM('Monthly Reports'[Active Certificates]),
        DATEADD('Monthly Reports'[LoadDate], -1, MONTH),
        ALL('Monthly Reports'[Institute])
    )
  )
)

I get weird results at the moment. If I choose to filter by date and add the entire date the following table is displayed, it shows the correct differences for the middle dates, however not for the final date:

When I filter by date hirarchy and months (like in the other table above) I get exactly the same results again.

Thanks, Vincenz

score 0 · Answer 1 · answered Nov 05 '20 at 14:00

In your calculation you use ALLEXCEPT. ALLEXCEPT removes the filters from the expanded table specified in the first argument(remove from all column in this table), keeping only the filters in the columns specified in the following arguments. That means you keep context from your matrix (July from your header). conditions are mutually exclusive.

You want to do the opposite thing, remove the filter from your "date" column. Use ALL('TableName'[ColumnName])

Your first measure should looks like:

Simple =
DIVIDE(CALCULATE ( SUM ( 'Monthly Reports'[Sales] ) )
    , CALCULATE (
        SUM ( 'Monthly Reports'[Sales] ),
        ALL ( 'Monthly Reports'[Company] )
    ))

Thanks for your help! I already implemented to changes as you said and now I'm getting a bit better results. However, two problems still remain. The display of Month Names in the columns and the wrong first and last Column. I changed the post accordingly! — Vincenz Mössenböck, Nov 05 '20 at 14:15

score 0 · Accepted Answer · answered Nov 05 '20 at 14:45

I solved my calculation and found a workaround for the wrongly displayed date hierarchy. Sometimes the answer to a question if you're not familiar with a programming language yet is simpler as one could expect.

Finished working code:

Market Share Prev Period = 
IF(
CALCULATE(
    SUM('Monthly Reports'[Sales]),
    PREVIOUSMONTH('Monthly Reports'[SaleDate])
) 
<> BLANK()
,
(
    DIVIDE(
        CALCULATE(
            SUM('Monthly Reports'[Sales])
        ), 
        CALCULATE(
            SUM('Monthly Reports'[Sales]),
            ALL('Monthly Reports'[Company])
        )
        ,
        0
    )
)
-
(
    DIVIDE(
        CALCULATE(
            SUM('Monthly Reports'[Sales]),
            PREVIOUSMONTH('Monthly Reports'[SaleDate])
        ), 
        CALCULATE(
            SUM('Monthly Reports'[Sales]),
            PREVIOUSMONTH('Monthly Reports'[SaleDate]),
            ALL('Monthly Reports'[Company])
        )
        ,
        0
    )
  ),
"-"
)

I figured out, that be using DateAdd(.., -1, Month) I only changed what was read, not in which column it goes. So I was always missing the most recent value, as off course it was not read by the -1 operation and I was missing the oldest report as there was none before off course. Basically I found the function: PREVIOUSMONTH (too simple to be true).

Furthermore, I added an IF clause before to check if I'm at the first column. If so, I'm dashing the value.

My only problem, which was left in the end, was that when displaying the date hierarchy I only saw dashes in all the fields of the matrix. However, if I displayed the whole date I could see all correct values. My workaround was to format the date with "mmmm". This displayed the same. I hope I will not need the date in any further step, as I will need to come back to this again.

So my result looked exactly perfect.

Power BI - Compare Measures over Period

2 Answers2