Need to pull data from one column value with filter in another column value

[Purni55]

I have three columns values say GrossSalesCY, EventDate and LastYearCorrespondingDate. Now, I have to create a new column called GrossSalesLY by getting the value from GrossSalesCY where LastYearCorrespondingDate = EventDate. 

Can someone please help with Dax?

[Anonymous]

Hi, @Purni55 
Based on your information, I create a table:

Maybe you can create a new column and try the following DAX:

GrossSalesLY = 
CALCULATE(
    SUM('Table'[GrossSalesCY]),
    FILTER(
        ALL('Table'),
        'Table'[LastYearCorrespondingDate] = EARLIER('Table'[EventDate])
    )
)

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Purni55]

Hi There, Thanks for the response. Yes I am looking for the same output. I have tried your DAX but it doesn't work with calculated column and throws error Calculate is not a function. 

I have tried creating a Calculated Measure instead with same DAX but doesn't satisfy the output. Can you please provide me DAX for Calculated Measure?

Thanks

[Anonymous · Accepted solution]

Hi, @Purni55 

Sorry for being so late to get back to you. Something might be missing from your Power BI Desktop. I'll send you the PBIX file, you can open it and see if there's anything wrong with the function. If you still have the above issues, I recommend that you upgrade your Power BI Desktop or reinstall it.

In PBIX, I've added a new measure, here is the DAX and preview:

GrossSalesLY Measure = 
CALCULATE(
    SUM('Table'[GrossSalesCY]),
    FILTER(
        ALL('Table'),
        'Table'[LastYearCorrespondingDate] = MAX('Table'[EventDate])
    )
)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.