Forum Discussion

NewPowerBIUser1's avatar
NewPowerBIUser1
New Member
2 years ago
Solved

Different conversion factors for different lines in a graph

Dear PowerBI Community,

Since this is my first post feel free to give me advice in case I missed anything.
 
I currently struggle with a problem which is - at least for me - not that easy to implement in PowerBI.


Input / Data:
I have a table with a few hundreds of thousands of rows:

ProductDate (dd.mm.yyyy)CurrencyValue
X1.08.2023EUR100
X2.08.2023CAD144
X1.08.2023USD106
X15.08.2023USD106
Y15.08.2023EUR100
X2.09.2023USD108
X10.09.2023CAD148
X31.7.2023EUR100
............

 

And a second one for the currency conversion to EUR based on the monthly average:

Date (dd.mm.yyyy)CurrencyConversion Factor
01.08.2023EUR1
01.08.2023USD1,06
01.08.2023CAD1,44
01.09.2023EUR1
01.09.2023USD1,08
01.09.2023CAD1,48
.........

 

Result:
What I wish to achieve is a graph showing the sales of a product "X" where each line represents a month with the X-Axis being the (working) days and Y-Axis being the yearly cumulated sales of this product X in Euro. 

 

Each month line needs to be completely calculated with their exchange rate from this month e.g.:
August line needs to calculate all the values from Jan, Feb, March, April, May, June, July and August with the August exchange rate from 1.08.2023. (Since I would need the cumulated sales sum to be completly calculated with this one exchange rate)

September line needs to calculate all the values from Jan, Feb, March, April, May, June, July and August, September with the September exchange rate from 1.09.2023.

The value points from the above data example would be:
31.07.2023 - 100

1.08.2023 - 300 which is the sum from past months = (100 EUR) + current month = (100 EUR + (106 USD / 1,06))

2.08.2023 - 400 = 300 + 144 CAD /1,44

15.08.2023 - 500 = 400  + 106 USD / 1,06 (Note: Product Y is ignored)

1.09.2023 - 493,59 due to new september conversion factor for past values = (200 EUR + (212 USD / 1,08) + (144 CAD / 1,48))   

2.09.2023 - 593,59 = 493,59 + 108 USD /1,08

10.09.2023 - 693,59 = 593,59 + 148 CAD / 1,48


Example Picture with more daily values:




Does someone have an idea on how to solve this?


Thanks a lot!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  NewPowerBIUser1 ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Create a date dimension table

    DimDate = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))

    2. Create a measure as below

    Cumulated sales = 
VAR _ADDCURRENCY =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "Rate",
            VAR _LASTYEAR =
                MAX ( DimDate[Year] )
            VAR _LASTMONTH =
                MAX ( DimDate[Month] )
            VAR _RATE = 
                 CALCULATE (
                    SUM ( 'Currency'[Conversion Factor] ),
                    FILTER (
                        'Currency',
                        YEAR ( [Date] ) = _LASTYEAR
                            && MONTH ( [Date] ) = _LASTMONTH
                            && 'Currency'[Currency] = EARLIER ( [Currency] )
                    )
                )
            RETURN
               _RATE
    )
RETURN
    SUMX (
        FILTER ( _ADDCURRENCY, [Date] <= MAX ( DimDate[Date] ) && [Product] = SELECTEDVALUE('Table'[Product]) ),
        DIVIDE ( [Value], [Rate] )
    )

    Best Regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  NewPowerBIUser1 ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Create a date dimension table

    DimDate = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))

    2. Create a measure as below

    Cumulated sales = 
VAR _ADDCURRENCY =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "Rate",
            VAR _LASTYEAR =
                MAX ( DimDate[Year] )
            VAR _LASTMONTH =
                MAX ( DimDate[Month] )
            VAR _RATE = 
                 CALCULATE (
                    SUM ( 'Currency'[Conversion Factor] ),
                    FILTER (
                        'Currency',
                        YEAR ( [Date] ) = _LASTYEAR
                            && MONTH ( [Date] ) = _LASTMONTH
                            && 'Currency'[Currency] = EARLIER ( [Currency] )
                    )
                )
            RETURN
               _RATE
    )
RETURN
    SUMX (
        FILTER ( _ADDCURRENCY, [Date] <= MAX ( DimDate[Date] ) && [Product] = SELECTEDVALUE('Table'[Product]) ),
        DIVIDE ( [Value], [Rate] )
    )

    Best Regards