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

Return sum of numerical data for each counterparty and its several currencies with date filters

Hi everyone, 

I need your help regarding Power BI because I'm quite new to it. I'm struggling to compute and show what I want.

I have imported my table from a database that looks like this: 

Screenshot 2021-04-10 at 22.04.30.png

 

What I want is to compute the sum of the Nominal for each Counterparty and each Currency, BUT it needs to respect the condition: start date <= date in the date table & end date > date in the date table that I created on Power Pivot.

Screenshot 2021-04-10 at 22.04.40.png

In the end, I want to show the result just like the below image, a  dynamic table (ex.if you click on EUR, it will show all EUR data)

Screenshot 2021-04-10 at 22.04.35.png

Moreover, after computing the sum of the nominal, I need to compute the difference between the two dates and the % variation of it.

 

On my side, I was able to get the sum I want but the problem is I created computed columns for each counterparty and currency like 'AZE EUR', 'AZE USD', in the Date table.

 

By doing this, I can't create a pie chart later that defines the % of EUR or USD owned by AZE and HUB counterparties. So no dynamic chart.

I hope I was clear enough. If not let me know 🙂

Thank you in advance for all your help.

 

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi, @Merry3010 

You need to create another table:

 

Bridge Table = 
GENERATE (
    CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2021, 12, 31 ) ),
    SELECTCOLUMNS (
        'Trade Table',
        "Counterparty", 'Trade Table'[Counterparty],"Currency",'Trade Table'[Currency],"Counterparty_Currency",'Trade Table'[Counterparty_Currency]
    )
)

 

Measure:

 

Sum of Nominal = 
VAR _currentdate =
    SELECTEDVALUE ( 'Bridge Table'[Date] )
RETURN
    SUMX (
        FILTER (
            'Trade Table',
            _currentdate >= 'Trade Table'[Start date]
                && _currentdate < 'Trade Table'[End date]
        ),
        'Trade Table'[Nominal]
    ) + 0

 

Result:

77.png

 

Please check my attached pbix file for more details.

If it doesn't meet your requirement, please let me know.

 

Best Regards,
Community Support Team _ Eason
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

11 REPLIES 11
v-easonf-msft
Community Support
Community Support

Hi, @Merry3010 

You need to create another table:

 

Bridge Table = 
GENERATE (
    CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2021, 12, 31 ) ),
    SELECTCOLUMNS (
        'Trade Table',
        "Counterparty", 'Trade Table'[Counterparty],"Currency",'Trade Table'[Currency],"Counterparty_Currency",'Trade Table'[Counterparty_Currency]
    )
)

 

Measure:

 

Sum of Nominal = 
VAR _currentdate =
    SELECTEDVALUE ( 'Bridge Table'[Date] )
RETURN
    SUMX (
        FILTER (
            'Trade Table',
            _currentdate >= 'Trade Table'[Start date]
                && _currentdate < 'Trade Table'[End date]
        ),
        'Trade Table'[Nominal]
    ) + 0

 

Result:

77.png

 

Please check my attached pbix file for more details.

If it doesn't meet your requirement, please let me know.

 

Best Regards,
Community Support Team _ Eason
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

Hi @v-easonf-msft ,

I have an issue creating a relationship from "many to many" between tables. I can only do "many to one". So I created a table that takes unique values (AZE-EUR, AZE-USD, HUB-EUR, HUB-USD) connecting the 'Trade table', and the 'Bridge table' and created the measures "sum of nominal" but didn't get the right sum. So I tried as a calculated column in the "bridge table". Still didn't get the right amount.

 

At work, we don't have the possibility to download the latest version of powerbi (I don't know, if it is related to the power bi version)

 

Hi, @Merry3010 

The 'Bridge Table' is created to replace the original 'Trade Table' to create a ‘many-to-many’ relationship.

Have you checked wherther the fields in your  matrix visual are all from the 'Bridge Table' rather than 'Trade Table'?

15.png

To avoid missing some important information, please share your current sample pbix file for testing.

 

Best Regards,
Community Support Team _ Eason

Hi, I made some modifications with your solution, and now it works.

 Thanks for your help 

Hi, Thank you for your quick reply.

I am not allowed to show a sample of my data, but I will create a new one with the same type of data for testing.

What I am struggling to do is creating the cardinality between the bridge table and the trade table because, on the bridge table I have no unique values in the counterparty_currency column. (This is the reason why I can't create a relationship between  'many to many')

176234604_336363804749329_1759953190572623262_n.jpg

Hi @v-easonf-msft , 

Sorry for the late reply! Thank you very much for your help.

I will try on my data and let you know if it works or not. 🙂 

 

 

daxer-almighty
Solution Sage
Solution Sage

 

// Assuming the model is correct
// which means the date table is
// DISCONNECTED from the fact table.
// Additionally, assuming that start
// and end date are always present,
// meaning there are no blanks.

[Your Measure] =
// This will work with any
// period of time selected from
// the date table, not only for
// one day. Please understand
// the logic of the measure to
// know what it does.
var MinDateSelected = MIN( 'Date Table'[Date] )
var MaxDateSelected = MAX( 'Date Table'[Date] )
// By the way, Currency and Counterparty should
// be a separate dimension in a proper model.
// They should not be sourced from the fact table.
// All columns in a fact table should be hidden
// since slicing can only be done via dimensions.
// If you deviate from this proper design,
// you may have problems (and sooner or later you
// certainly will).
var IsOneCurrencyVisible = HASONEFILTER( 'Trade Table'[Currency] )
var Result =
    if( IsOneCurrencyVisible,
        SUMX(
            'Trade Table',
            var vNominal = 'Trade Table'[Nominal]
            var vStartDate = 'Trade Table'[Start Date]
            var vEndDate = 'Trade Table'[End Date]
            return
                vNominal
                * (
                    vStartDate <= MinDateSelected
                    &&
                    MaxDateSelected < vEndDate
                )
        )
    )
return
    Result

 

Hi Daxer, 

Thank you for your answer. However, the measure doesn't give any output. I only have 0 everywhere.

I have also followed your advise on slicing my counterparty & currency data from the fact table into a separate dimension, based on their trade id. 

 

NB: The date table is not connected to any table & In my real table ,all nominals are different. In the table that I showed above I wrote '100' everywhere as nominal to make it simple

@Merry3010 

 

If you've changed the model, you have to change the measure accordingly, of course.

Hi Daxer, 

I've tried it on my initial model and it didn't work so I tried on my new model with separate counterparty and currency while changing the measurer accordingly. And didn't work.

Hi, @Merry3010 

Did you solve your problem?

If not, please share your sample pbix file for testing.This will better help us understand rhe  problem.

 

Best Regards,
Community Support Team _ Eason

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.