Forum Discussion

raj111983's avatar
raj111983
Frequent Visitor
1 year ago
Solved

Help with Intersect Function

HI, I just new to Power BI and having difficult time in using Intersect function. Basically I have a single Sales table with multi year data. I created an unrelated Dates table from the Sales  table ...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi raj111983 ,

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

    Chart PTran = 
VAR _selyear =
    SELECTEDVALUE ( 'Dates'[Date].[Year] )
VAR _prevyear =
    SELECTEDVALUE ( 'Dates'[Date].[Year] ) - 1
VAR _month =
    SELECTEDVALUE ( 'Sales'[Date].[Month] )
VAR _CSource =
    CALCULATETABLE (
        VALUES ( 'Sales'[Source ID] ),
        FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[Date].[Year] = _selyear )
    )
VAR _PSource =
    CALCULATETABLE (
        VALUES ( 'Sales'[Source ID] ),
        FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[Date].[Year] = _prevyear )
    )
VAR _tab =
    INTERSECT ( _CSource, _PSource )
RETURN
    CALCULATE (
        SUM ( Sales[Measure Value] ),
        FILTER (
            'Sales',
            'Sales'[Date].[Year] = _prevyear
                && 'Sales'[Date].[Month] = _month
                && 'Sales'[Measure Name] = "Volume"
                && 'Sales'[Source ID] IN _tab
        )
    )

    Best Regards

raj111983's avatar
raj111983
Frequent Visitor
1 year ago

Hi,

Sample data is in Drop Box folder. Please le tme know if you can access it.

https://www.dropbox.com/scl/fi/q9lssc3mam3dji5ohohq6/Sample.xlsx?rlkey=p3ylb5r35d0ar0be66zsu4zmg&st=yduycsp8&dl=0

As you can see the data, Source ID AHA is present in 2024 so that shouldnt be included in the chart for current or previous year. Same with Source ID FKA, data is not availabel for Jan2023 so it shouldnt be included in Jan2024 sum. This is to Apples-Apples comparisionby Source ID and Month. I want to show full year last year and current year. But when I use intersect function, its only showing the data till Septemper.

Thank you!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi raj111983 ,

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

    Chart PTran = 
VAR _selyear =
    SELECTEDVALUE ( 'Dates'[Date].[Year] )
VAR _prevyear =
    SELECTEDVALUE ( 'Dates'[Date].[Year] ) - 1
VAR _month =
    SELECTEDVALUE ( 'Sales'[Date].[Month] )
VAR _CSource =
    CALCULATETABLE (
        VALUES ( 'Sales'[Source ID] ),
        FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[Date].[Year] = _selyear )
    )
VAR _PSource =
    CALCULATETABLE (
        VALUES ( 'Sales'[Source ID] ),
        FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[Date].[Year] = _prevyear )
    )
VAR _tab =
    INTERSECT ( _CSource, _PSource )
RETURN
    CALCULATE (
        SUM ( Sales[Measure Value] ),
        FILTER (
            'Sales',
            'Sales'[Date].[Year] = _prevyear
                && 'Sales'[Date].[Month] = _month
                && 'Sales'[Measure Name] = "Volume"
                && 'Sales'[Source ID] IN _tab
        )
    )

    Best Regards

    • raj111983's avatar
      raj111983
      Frequent Visitor
      1 year ago

      Thank you!! Worked Perfectly!!