Forum Discussion

NewPowerBIUser1's avatar
NewPowerBIUser1
New Member
3 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 imp...
Need Help
  • Anonymous's avatar
    Anonymous
    3 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

Anonymous's avatar
Anonymous
Not applicable
3 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