Help with Intersect Function

[raj111983]

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 to use for Slicers. I was able to create a measure for line chart but the chart is not displaying full year for last year. 

The expression I have is below. I am trying to find the Source ID that is in both years for the corresponding month and calculate the sum of Sales.

What I am seeing is below

What I want to see is somthing like below. Full year data for last year and only the data thats available fo rcurrent year. I know its not data related issue as I already checked it. Is Intersect correct method to use in this case? I also tried to create multi line chart with two measures for last year and current year without any luck.

Your help is appriciated

[v-yiruan-msft]

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]

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.

[PhilipTreacy]

@raj111983 

Please supply your data, it makes it so much easier to debug than just some images.

Thanks

Phil

[raj111983]

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=...

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!!

[raj111983]

Hi,

Here is the pbix with sample data. I wanted the previous year 

https://www.dropbox.com/scl/fi/ikzyz7f7r8ofzlfqikmua/Sample.pbix?rlkey=y7hd05n7gn8lilow5ylfnguak&st=...

[v-yiruan-msft · Accepted solution]

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]

Thank you!! Worked Perfectly!!