cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular 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
Microsoft
Microsoft

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.

View solution in original post

@


@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!!!

View solution in original post

2 REPLIES 2
Microsoft
Microsoft

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.

View solution in original post

@


@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!!!

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors