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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi raj111983 ,

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

Sel year values = 
VAR _selyear =
    SELECTEDVALUE ( 'Dates'[Year] )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Measure Value] ),
        FILTER (
            ALLSELECTED ( 'Sales' ),
            'Sales'[Measure Name] = "Volume"
                && 'Sales'[Year] = _selyear
                && FORMAT ( 'Sales'[Date], "mmmm" ) = SELECTEDVALUE ( 'Sales'[Date].[Month] )
        )
    )
Pre year values = 
VAR _selyear =
    SELECTEDVALUE ( 'Dates'[Year] )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Measure Value] ),
        FILTER (
            ALLSELECTED ( 'Sales' ),
            'Sales'[Measure Name] = "Volume"
                && 'Sales'[Year] = _selyear - 1
                && FORMAT ( 'Sales'[Date], "mmmm" ) = SELECTEDVALUE ( 'Sales'[Date].[Month] )
        )
    )

Best Regards

raj111983's avatar
raj111983
Frequent Visitor
1 year ago

Hi,

 

THank you very much for the reply. But that is not what I was looking for. I posted a sample file and more explanation of what I was looking for. I am trying calculate current year and previous for onlt the Source ID's that are reporting data for corrsponding months in each year. Lets say, if Source ID "A" is only reporting data for January and March for one of the years then that Source ID should only be included for Jan and March for both current and previous years.