Forum Discussion

tonijj's avatar
tonijj
Helper IV
7 years ago

Compare Data within the same table

Hi,

I just got stuck on an issue, that I guess should be easy, but I just cant seem to solve it. I have searched the forum and tried several of the threads out there, but…just no luck ☹

So, I have a mastertable with information, including volume per Month.

 

What I want to achieve:

I want to be able to compare Volumes compared to different months. For instance; Compare September volumes vs October volumes. Also, between years if possible. See picture below, I want to be able to see (A) compared to the specific months I choose. 

 

Some factfulness:

  • Date – There is a datekey in each table with relationship to to a master datetable
  • Unique ID . There is a unique ID for a Service structure, called “SID”.

 

Need Help

13 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI tonijj,

     

    You can refer to below steps to create a matrix visual to achieve your requirement:

     

    Steps:

    1. Create a matrix visual with 'Service'[Service] as row, 'Volume'[Datakey].[Month] as column, 'Volume'[Volume] as value.

    2. Write a measure to calculate 'MOM diff' and drag to value fields.

    MOM Diff = 
VAR currDate =
    MAX ( Volume[DateKey] )
VAR currTotal =
    CALCULATE (
        SUM ( Volume[Volume] ),
        FILTER (
            ALLSELECTED ( Volume ),
            FORMAT ( [DateKey], "mm/yyyy" ) = FORMAT ( currDate, "mm/yyyy" )
        ),
        VALUES ( Volume[SID] )
    )
VAR prevTotal =
    CALCULATE (
        SUM ( Volume[Volume] ),
        FILTER (
            ALLSELECTED ( Volume ),
            FORMAT ( [DateKey], "mm/yyyy" )
                = FORMAT ( DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, 1 ), "mm/yyyy" )
        ),
        VALUES ( Volume[SID] )
    )
RETURN
    IF ( prevTotal <> BLANK (), currTotal - prevTotal )

     

    Result:

     

    Regards,
    Xiaoxin Sheng

    • tonijj's avatar
      tonijj
      Helper IV
      7 years ago

      Hi Anonymous

       

      Im almost there I think, however, I think I need that last nudge. 

       

      It doesnt show any numbers unfortunately, even though it is a volume difference. See screenshot.

       

      I have uploaded the pbix file for reference, I guess its easier to understand instead of using a simple example as I used in my first post. 

       

      Link: File on Onedrive