Search code examples
timepowerbimeasurerelated-content

Power BI - Compare Measures over Period

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. enter image description here

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: enter image description here

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

enter image description here

Thanks, Vincenz

Solution

  • 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. enter image description here