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?

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" ?
Highlighted
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
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,167)