USERELATIONSHIP with inactive many-to-many

[Anonymous]

Hi Everyone,

For my client I am trying to calculate the Revenue from last year.
It concerns retail, so comparison with last year is a bit tricky. It has to compare first monday of current year with the first monday prior year etc.

For this we made an extra column in our calendar table which holds this "DatePY".

Due to the fact that years do not hold the same amount of "Mondays",  "Tuesdays",... some values in the "DatePY" are empty.

So we have 2 tables: calendar and transactions. Which are linked as follows.

Calendar(date)     1 ---- * Transactions(date); active relationship

Calendar(DatePY) *-----* Transactions(date); inactive relationship

I calculate my Revenue from PY as follows:
Calculate(Sum(Transactions(Revenue), USERELATIONSHIP(Calendar(DatePY), Transactions(date)))

But this returns the exact same amount as the calculation for current year (just sum of Transactions(Revenue))

I already found out that when I remove the blanks from the DatePY column, it works (but this not desirable because this removes rows from the calendar table).

In the past we fixed this by duplicating the Transactions table, but this is also not desirable.

Can anyone help me out?

Much Appreciated!

[V-lianl-msft]

Hi @Anonymous ,

It is very difficult to analyze without looking at the data and just by imagining. See if you can paste the image of the chart representing any sample data.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216 

Please remove any sensitive data before uploading.

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

[amitchandak]

@Anonymous , I would suggest using time intelligence with date table

examples

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA