Different conversion factors for different lines in a graph

[NewPowerBIUser1]

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:

Product	Date (dd.mm.yyyy)	Currency	Value
X	1.08.2023	EUR	100
X	2.08.2023	CAD	144
X	1.08.2023	USD	106
X	15.08.2023	USD	106
Y	15.08.2023	EUR	100
X	2.09.2023	USD	108
X	10.09.2023	CAD	148
X	31.7.2023	EUR	100
...	...	...	...

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

Date (dd.mm.yyyy)	Currency	Conversion Factor
01.08.2023	EUR	1
01.08.2023	USD	1,06
01.08.2023	CAD	1,44
01.09.2023	EUR	1
01.09.2023	USD	1,08
01.09.2023	CAD	1,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!

[Anonymous · Accepted solution]

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