Calculating last years value when current period has a value

[Anonymous]

Hi,

I am trying to calculate the last period's value when the current period has a value. This is to be able to calculate a Like-for-Like calculation and an organic growth on top of that. What we need to do is to look at this current period and see which members were active previous period. If they were active, I'd like to compare the two revenues against each other to be able to calculate a YoY measure. 

With this, there are a couple of things to take in mind: Start Date, End Date, Purchase Date, Turnover

The issue I am having is either: the row subtotals are wrong, or that the filtering won't take into account a member's starting/ending date.

I've come up with multiple formulas all becoming wrong. Currently I have: 

CALCULATE( [Test OG Year], SAMEPERIODLASTYEAR('Date'[Date]), VALUES(Testing_Table[Member]) )

The issue with the code above is that it doesn't take the start/end date of the member into calculation, but the row totals are correct. 

I have also tried an IF solution:

IF(Testing_Table[Test OG Year] > 0,
CALCULATE( SUMX( FILTER( 'Testing_Table', 'Testing_Table'[StartDate].[Date] <= MIN('Date'[Date]) && ( 'Testing_Table'[EndDate].[Date] >= MAX('Date'[Date]) || ISBLANK('Testing_Table'[EndDate].[Date]) ) ) , [Test OG Year] ), SAMEPERIODLASTYEAR('Date'[Date]), VALUES(Testing_Table[Member]) ), BLANK()) G Year] ), SAMEPERIODLASTYEAR('Date'[Date]) ), BLANK())

This one gets ridiculously close to the solution, but it does somehow add an empty row in Q2 April for Member1 into the row subtotal, even though it does not display it in a matrix due to it not fulfilling the criterias. This makes me extremely confused as it's the only issue I am having. See below:

 I have my example data here: testing data

Please note that I am relating the table to a standard Date table as well, on the PurchaseDate.

EDIT: Worth mentioning is that if an EndDate is within a month, we should NOT include that month. Hence the example where a member leaves at 2020-04-15. The april data should NOT be included in the calculations

[amitchandak]

@Anonymous , for multiple dates you join with same date table. You will have one active join and other inactive. Then use userelation activate those

refer my blog : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

or video:https://www.youtube.com/watch?v=e6Y-l_JtCq4

For last year, you can also use

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

I'm not sure how USERELATION would help me, I'm not sure how to use it even with looking through your material. 

I am not in need of StartDate/EndDate as slicers. I just need to filter it properly through FILTER or other expressions, which I've been able to do with all my calculations except this one looking at the previous year. It's tricky since I first need to use FILTER on the StartDate/EndDates, and then changing the context to previous year. 

The slicing still only needs to be done on purchase date. Maybe I am wrong but I can't see how USERELATION will help.