Difference between Column Values in Matrix Visual

[adhumal2]

Dear Experts,

I have 2 data files for month Mar and Apr. I want to create a matrix visual wherein I should be able to calculate the difference between 2 columns in the matrix visual

Here is how the data looks like for both Months and even the output expected:

Mar -20

Mar

Apr -20

Apr

Output Expected

Output Expected

In PowerBI, I did append both Mar and April data and created a matrix visual with 'Business' in rows and 'Months' in columns and 'Sales Units' and 'Sales Value' in the Values. Now I want to calculate below:

• Difference between 2 months for Sales Units
• Difference between 2 months for Sales Value

Furthermore,

Is there a better solution available for above problem than the Matrix Visual?

I have attached the sample file.https://drive.google.com/file/d/1PkgPWGSLuVS1eS0dXupZ8EmDh-lXJRW-/view?usp=sharing 

Please Help. Thanks a lot!

[Icey · Accepted solution]

Hi @adhumal2 ,

How about this?

1. Enter data.

SalesColumn Table:

DiffTable:

2. Create a table.

MonthTable = UNION ( VALUES ( 'Mar 20'[Month ] ), DiffTable )

3. Create measures.

Diff_Sales_Units = 
VAR MaxDate =
    MAXX ( VALUES ( 'Mar 20'[Month ] ), [Month ] )
VAR MinDate =
    MINX ( VALUES ( 'Mar 20'[Month ] ), [Month ] )
RETURN
    CALCULATE (
        SUM ( 'Mar 20'[Sales Units] ),
        FILTER ( 'Mar 20', 'Mar 20'[Month ] = MaxDate )
    )
        - CALCULATE (
            SUM ( 'Mar 20'[Sales Units] ),
            FILTER ( 'Mar 20', 'Mar 20'[Month ] = MinDate )
        )

Diff_Sales_Value = 
VAR MaxDate =
    MAXX ( VALUES ( 'Mar 20'[Month ] ), [Month ] )
VAR MinDate =
    MINX ( VALUES ( 'Mar 20'[Month ] ), [Month ] )
RETURN
    CALCULATE (
        SUM ( 'Mar 20'[Sales Value] ),
        FILTER ( 'Mar 20', 'Mar 20'[Month ] = MaxDate )
    )
        - CALCULATE (
            SUM ( 'Mar 20'[Sales Value] ),
            FILTER ( 'Mar 20', 'Mar 20'[Month ] = MinDate )
        )

Measure = 
IF (
    MAX ( MonthTable[Month ] ) <> "Difference",
    SWITCH (
        MAX ( SalesColumn[SalesColumn] ),
        "Sales Units", CALCULATE (
            SUM ( 'Mar 20'[Sales Units] ),
            FILTER (
                'Mar 20',
                'Mar 20'[Month ] = CONVERT ( SELECTEDVALUE ( MonthTable[Month ] ), DATETIME )
            )
        ),
        "Sales Value", CALCULATE (
            SUM ( 'Mar 20'[Sales Value] ),
            FILTER (
                'Mar 20',
                'Mar 20'[Month ] = CONVERT ( SELECTEDVALUE ( MonthTable[Month ] ), DATETIME )
            )
        )
    ),
    IF (
        MAX ( MonthTable[Month ] ) = "Difference",
        SWITCH (
            MAX ( SalesColumn[SalesColumn] ),
            "Sales Units", [Diff_Sales_Units],
            "Sales Value", [Diff_Sales_Value]
        )
    )
)

4. Create a Matrix visual.

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@adhumal2 , there nothing like diff between two columns you have to create diff between month like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
diff = [MTD Sales]-[last MTD Sales]

You can use this measure as diff

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[adhumal2]

@amitchandak - Thank you for your reply.

I used above formula MTD Sales but it does return to me a blank column in the visual. Also, can you please also explain me like why have you mentioned the formula for last MTD (Complete) sales

[amitchandak]

@adhumal2 , Are you date table and month year in visual from that date table?

MTD complete for the case where you choose a date in middle of month and want last month to be complete

[adhumal2]

@amitchandakYes, I do have a date table ready.

Also, In the columns i have - current month values and previous month values based on the measure created. I do not have months from date table in the visual.

If i create a measure like - 

Current Month= CALCULATE(SUM(Append1[Sales Value (CHF)]),FILTER(Append1,Append1[Month]=MAX(Append1[Month])))

Previous Month = CALCULATE(SUM(Append1[Sales Value (CHF)]),FILTER(Append1,Append1[Month]=MIN(Append1[Month])))

It works , but I want to follow your solution to verify these details

[amitchandak]

@adhumal2 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[adhumal2]

@amitchandak- Sure, Here attched is the sample data and the output expected (in the excel sheets). Many Thanks in advance for your kind efforts.

https://drive.google.com/file/d/1PkgPWGSLuVS1eS0dXupZ8EmDh-lXJRW-/view?usp=sharing