Previous year calculations bring back current year values :/ - help

[vanessafvg]

I posted this in another thread but highlighted the issue of it not plotting correctly on a line graph - however the issue actually is both of these calcutions ie

PY Revenue = CALCULATE([Revenue], SAMEPERIODLASTYEAR(all('Fiscal Date'[Date])))

PY 2 = CALCULATE([Revenue], PARALLELPERIOD(ALL('Fiscal Date'[Date]), -1, YEAR))

bring back exaclty the same data as this

Revenue = (CALCULATE(Sum(Registrations[MRR])) + CALCULATE(sum(Sales[RevenueNettAmount])))

what am i doing wrong, could this be a cross fact issue.  I have 2 facts linking  together through dimensions.  However its only the date dimension that is the linking bit for these 2 which has a one to many relationship for each fact in a single direction.

 

[mattbrice]

Remove the ALL function call inside the SAMEPERIODLASTYEAR and PARALLELPERIOD calls.  

[vanessafvg]

i had that previously but it brings back nothing - its because i have this filter on my dashboard

this is what i have changed to 

PY Revenue = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Fiscal Date'[Date]))

PY 2 = CALCULATE([Revenue], PARALLELPERIOD('Fiscal Date'[Date], -1, YEAR))

and as you can see now its empty when i change this, i am baffled... 

 

[mattbrice]

Does your 'Fiscal Date' have dates in it for the prior fiscal year(it must for time intelligence to work)? Is your 'Fiscal Date' calendar table related to the fact table via columns of type Date?   if not, can you try this:

PY Revenue = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Fiscal Date'[Date]), ALL('Fiscal Calendar') )

[vanessafvg]

that definitely shifted something however my measures are behaving different,  rushing off to a weekend away will figure it out monday, thanks for the help!

[v-qiuyu-msft]

@vanessafvg Is the issue solved now?

[vanessafvg]

no 😞  trying a variety of things but its just not working - i really can't figure out what i am doing wrong, just throwing in things now to see what i might be doing wrong.

Revenue = (CALCULATE(Sum(Registrations[MRR])) + CALCULATE(sum(Sales[RevenueNettAmount]))) - this is the base calculation

PY Revenue = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Fiscal Date'[TheDate].[Date]), ALL('Fiscal Date') )

PY 2 = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Fiscal Date'[TheDate].[Date]), 'Fiscal Date'[Calendar Year] )

PY 3 = CALCULATE([Revenue], PARALLELPERIOD(all('Fiscal Date'[TheDate].[Date]), -1, YEAR))

I do have a filter on the page which is only looking at the current fiscal year, but there is definitely data in my model that stretches back beyond the fiscal year which i thought the ALL would resolve.   It does resolve it in PY Revenue however it doens't bring it back per day it just flat lines it in one aggregaton for all time.

Dont know if this makes sense?

[MalS]

I remember this problem from your other thread. I think this must be related to the relationships you have set up.

Can you share the .pbix file (or en extract from it?). Without that it's hard to troubleshoot.

[vanessafvg]

no unfortunately i can't. but i can tell you that i have 2 fact tables (if you look at the revenue measure its the combination of  a measure from these 2 facts), both fact tables have a  single many to one relationship with the date in fiscal date

 

[MalS]

Are you able to get it working ok by using revenues from just one table? 

For example:

Revenue = sum(Sales[RevenueNettAmount])

PY Revenue = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Fiscal Date'[TheDate]))

Can you also post a screenshot of the Manage Relationships window?

[vanessafvg]

now it brings back nothing 🙂 if i put all in, it just duplicated the revenue figure

[MalS]

Hmm - ok, so the last thing I would check before giving up (!) is the date field. 

Can you try creating a new date table. You could do a quick one by clicking on Modeling > New Table, then enter:

Calendar = 
CALENDAR (DATE(2010,1,1), DATE(2017,12,31))

Then create a relationship between this date field and your date field in the Sales table. 

On a new page, drag this new Date field to a Table Visualization, and drag the Revenue and PY Revenue measures to the same table. Remove all filters. 

Does that show anything for PY Revenue?

[vanessafvg]

thanks

i added the table.  I also removed all links to all other tables and only joined sales to the new calendar table.  

Revenue = CALCULATE(sum(Sales[RevenueNettAmount])) 

PY Revenue = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Calendar'[Date]))

PY2 Revenue = CALCULATE([Revenue], PARALLELPERIOD('Calendar'[Date],-1,YEAR)) (gave the same results)

[MalS]

Ah, ok. So you have some missing dates in the Sales table. Can you Edit Queries and filter those blanks out?

[vanessafvg]

thats a good point, ill give that a bash, i did post the manage relationships, in the last post.

[vanessafvg]

cant believe no one can help with these - anyone?