cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonijj Member
Member

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”.

 

compare data.png

13 REPLIES 13
Community Support Team
Community Support Team

Re: Compare Data within the same table

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:

4.PNG

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
tonijj Member
Member

Re: Compare Data within the same table

Hi @v-shex-msft

 

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

 

CompareDataWithTheSameTable 0.1 screenshot.png

 

 

tonijj Member
Member

Re: Compare Data within the same table

@v-shex-msft Any ideas?

Highlighted
Community Support Team
Community Support Team

Re: Compare Data within the same table

HI @tonijj,

 

I can't download sample file from your link, can you fix it?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
tonijj Member
Member

Re: Compare Data within the same table

Apologies... somehow Onedrive cant share to outside my org. 

 

Try this Link

Community Support Team
Community Support Team

Re: Compare Data within the same table

Hi @tonijj,

 

After play with your sample file, I found you modify my formula and use 'current date' to compare with 'current date'.

I modify the formula to let it works, please try to use following measure formulas if it suitable for your requirement:

MOM Diff =
VAR currDate =
    MAX ( DateTable[DateKey] )
VAR currTotal =
    CALCULATE (
        SUM ( DetailedVolumes[Volume] ),
        FILTER (
            ALLSELECTED ( DetailedVolumes ),
            MONTH ( [DateKey] ) = MONTH ( currDate )
        ),
        VALUES ( Services[SIN] )
    )
VAR prevTotal =
    CALCULATE (
        SUM ( DetailedVolumes[Volume] ),
        FILTER (
            ALLSELECTED ( DetailedVolumes ),
            MONTH ( [DateKey] )
                = MONTH ( currDate ) - 1
        ),
        VALUES ( Services[SIN] )
    )
RETURN
    IF ( prevtotal <> BLANK (), currTotal - prevTotal )

11.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
tonijj Member
Member

Re: Compare Data within the same table

That is just perfect, works like a charm!

 

Thanks so much for the help!

 

Now, if possible, please forward the feedback internally that something like this should be available as a quick measure in future releases 🙂 

tonijj Member
Member

Re: Compare Data within the same table

One more thing.... (feel like Steve Jobs here...) @v-shex-msft: It works perfect per SIN level (as the formula suggest) but if I drill down on "HostName" level, which can be found in table "DetailedVolumes" it breaks. Is there a way to modify it so it would work on both levels, both "Host Name" and "SIN" ?
Community Support Team
Community Support Team

Re: Compare Data within the same table

HI @tonijj,

 

I don't think quick measure can generate similar dax formula.

 

>>Is there a way to modify it so it would work on both levels, both "Host Name" and "SIN" ?

You can try to add values function to apply filter effect on that formula:

MOM Diff =
VAR currDate =
    MAX ( DateTable[DateKey] )
VAR currTotal =
    CALCULATE (
        SUM ( DetailedVolumes[Volume] ),
        FILTER (
            ALLSELECTED ( DetailedVolumes ),
            MONTH ( [DateKey] ) = MONTH ( currDate )
        ),
        VALUES ( Services[SIN] )
    )
VAR prevTotal =
    CALCULATE (
        SUM ( DetailedVolumes[Volume] ),
        FILTER (
            ALLSELECTED ( DetailedVolumes ),
            MONTH ( [DateKey] )
                = MONTH ( currDate ) - 1
        ),
        VALUES ( Services[SIN] ),
        VALUES ( Service[Host Name] )
    )
RETURN
    IF ( prevtotal <> BLANK (), currTotal - prevTotal )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 325 members 3,471 guests
Please welcome our newest community members: