cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Filipp Frequent Visitor
Frequent Visitor

Difference from previous period in matrix

Hey guys,

 

I have a table with 5 columns:
RPT_DATE - reporting date
POS_TYPE - position type
CNUM - customer number
BSAACID - account number
OVP_CCY - position amount

 

The table consists of the data as of 2 dates. I want to make a matrix with RPT_DATE in columns and be able to view the absolute difference between 2 dates on every level. So basically I want this difference to be shown like a total column, ie I want only 3 columns in this table - data as of date 1, data as of date 2, difference. I put RPT_DATE as a column, POS_TYPE, CNUM, BSAACID as rows and the following formula in values field:

test_pos = if(min(D1[RPT_DATE])=max(D1[RPT_DATE]);CALCULATE(sum(D1[OVP_CCY]);FILTER(D1;D1[RPT_DATE]=MAX(D1[RPT_DATE])));CALCULATE(sum(D1[OVP_CCY]);FILTER(D1;D1[RPT_DATE]=MAX(D1[RPT_DATE])))-CALCULATE(sum(D1[OVP_CCY]);FILTER(D1;D1[RPT_DATE]=MIN(D1[RPT_DATE]))))

It works pretty good except cases when the data as of the date 2 are missing. In this cases if function returs True and I get positive difference instead of negative:

 

image.png
I believe there are easier ways to achieve my goal, please suggest what can be done.

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Difference from previous period in matrix

Hi @Filipp,

 

The reason is that one value of the date is missed in the source data. Then the MIN and MAX return the same value. You can try this formula.

 

test_pos 2 =
VAR maxDate =
    CALCULATE ( MAX ( 'D1'[RPT_DATE] ); ALL ( D1 ) )
VAR minDate =
    CALCULATE ( MIN ( 'D1'[RPT_DATE] ); ALL ( d1 ) )
RETURN
    IF (
        HASONEFILTER ( D1[RPT_DATE] );
        SUM ( D1[OVP_CCY] );
        CALCULATE ( SUM ( D1[OVP_CCY] ); FILTER ( D1; D1[RPT_DATE] = maxDate ) )
            - CALCULATE ( SUM ( D1[OVP_CCY] ); FILTER ( D1; D1[RPT_DATE] = minDate ) )
    )

Difference from previous period in matrix.jpg

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

Re: Difference from previous period in matrix

@


@v-jiascu-msft wrote:

Hi @Filipp,

 

The reason is that one value of the date is missed in the source data. Then the MIN and MAX return the same value. You can try this formula.

 

test_pos 2 =
VAR maxDate =
    CALCULATE ( MAX ( 'D1'[RPT_DATE] ); ALL ( D1 ) )
VAR minDate =
    CALCULATE ( MIN ( 'D1'[RPT_DATE] ); ALL ( d1 ) )
RETURN
    IF (
        HASONEFILTER ( D1[RPT_DATE] );
        SUM ( D1[OVP_CCY] );
        CALCULATE ( SUM ( D1[OVP_CCY] ); FILTER ( D1; D1[RPT_DATE] = maxDate ) )
            - CALCULATE ( SUM ( D1[OVP_CCY] ); FILTER ( D1; D1[RPT_DATE] = minDate ) )
    )

Difference from previous period in matrix.jpg

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale


@v-jiascu-msft, Hi Dale! Thank you very much, it works perfectly!!!

2 REPLIES 2
v-jiascu-msft Super Contributor
Super Contributor

Re: Difference from previous period in matrix

Hi @Filipp,

 

The reason is that one value of the date is missed in the source data. Then the MIN and MAX return the same value. You can try this formula.

 

test_pos 2 =
VAR maxDate =
    CALCULATE ( MAX ( 'D1'[RPT_DATE] ); ALL ( D1 ) )
VAR minDate =
    CALCULATE ( MIN ( 'D1'[RPT_DATE] ); ALL ( d1 ) )
RETURN
    IF (
        HASONEFILTER ( D1[RPT_DATE] );
        SUM ( D1[OVP_CCY] );
        CALCULATE ( SUM ( D1[OVP_CCY] ); FILTER ( D1; D1[RPT_DATE] = maxDate ) )
            - CALCULATE ( SUM ( D1[OVP_CCY] ); FILTER ( D1; D1[RPT_DATE] = minDate ) )
    )

Difference from previous period in matrix.jpg

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

Re: Difference from previous period in matrix

@


@v-jiascu-msft wrote:

Hi @Filipp,

 

The reason is that one value of the date is missed in the source data. Then the MIN and MAX return the same value. You can try this formula.

 

test_pos 2 =
VAR maxDate =
    CALCULATE ( MAX ( 'D1'[RPT_DATE] ); ALL ( D1 ) )
VAR minDate =
    CALCULATE ( MIN ( 'D1'[RPT_DATE] ); ALL ( d1 ) )
RETURN
    IF (
        HASONEFILTER ( D1[RPT_DATE] );
        SUM ( D1[OVP_CCY] );
        CALCULATE ( SUM ( D1[OVP_CCY] ); FILTER ( D1; D1[RPT_DATE] = maxDate ) )
            - CALCULATE ( SUM ( D1[OVP_CCY] ); FILTER ( D1; D1[RPT_DATE] = minDate ) )
    )

Difference from previous period in matrix.jpg

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale


@v-jiascu-msft, Hi Dale! Thank you very much, it works perfectly!!!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 278 members 3,015 guests
Please welcome our newest community members: