cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MTracy Frequent Visitor
Frequent Visitor

Rebuilding report - different results with SAMEPERIODLASTYEAR measure... why?

 

I've had a report based on Support Ticket data that used multiple tables due to different categories of tickets we have.  Recently, I learned how to merge them into one "master" table using SQL and Merge in Power BI.   Now I'm rebuilding my report, page by page.   I've hit a roadblock when attempting to recreate the comparison of ticket volume this year versus last year (working model only uses my "IncidentRequests" and "DimDate" tables).   Using Sam McKay's YouTube method of calculated measures worked in the first .pbix report, but fails on the second, and I cannot tell why.

 

The first measure counts the total tickets:

Total Tickets = COUNTROWS( IncidentsRequests )

The second measure counts just the tickets from the previous year (system went live Feb 2016):

Total Tickets LY = CALCULATE( [Total Tickets], SAMEPERIODLASTYEAR( 'Dim Date'[Date] ) )

The final measure found the difference between the two:

Diff. Between TY and LY = IF( ISBLANK( [Total Tickets LY] ), BLANK(), [Total Tickets] - [Total Tickets LY] )

 The results in a simple table gave me what I wanted:

YoY Old.PNG

 

Note how 2016 monthly ticket counts are now repeated next to the same 2017 month?  That's the expected outcome.

 

Now in the NEW report, the new "Master" table is the data table used for this, against a copy of the same DimDate table, same main relationship between.  I've copied/pasted the previous measures (adding a "version # prefix to troubleshoot different calcs), but getting different results in the table:

YoY New.PNG

 

The total at the bottom in "1TotalTickets" column is accurate, as is the Total in the next "1TotalTicketsLY" column... but there's no data in the column above it... no monthly breakdown.  (remember, numbers are higher due to merged ticket tables)  Trying different measures in columns further to the right gets me accurate counts of the tickets from each different year, but does not help me compare them on a "same month" basis.  Other measure attempts are failures, giving empty columns

 

I can't think of any fundamental differences between the two reports' structure, though I do have a second relationship drawn between the Master and Date tables for closed ticket date reporting. (can't be caused by that, right?)  So what's causing the copied/pasted measures to behave differently in the new report?   Most importantly, I want to be able to create the "month-against-month" dimension.  What am I missing here?

 

Thanks in advance for any and all help/advice!

 

8 REPLIES 8
Microsoft v-jiascu-msft
Microsoft

Re: Rebuilding report - different results with SAMEPERIODLASTYEAR measure... why?

Hi @MTracy,

 

1. Which table are the fields [Year] and [Month] of the visual from? I afraid they are from the table "IncidentsRequests".

2. Which relationship is active as you say there are two relationships?

 

The two above are the possible causes. Please share the pbix file if the issue is still there. A dummy one is enough.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MTracy Frequent Visitor
Frequent Visitor

Re: Rebuilding report - different results with SAMEPERIODLASTYEAR measure... why?

Hi, thanks for the response!   Answers as follows:

 

1.  The [Year] and [Month] fields are from the "Dim Date" table, the calendar table I use for every pbix report, including the previous report I'm trying to repeat the success of.

 

2.  The active relationship is drawn exactly the same as the previous report.  Here is the old report (measure is working)...

 

 YoY Old Relationship.png

 

 ... and here is the new report (measure is NOT working)...

 

YoY New Relationship.png

 

The relationships are duplicated exactly, aside from the inactive secondary relationship. (hoping THAT isn't the problem)

 

 

Unfortunately I'm unable to share the pbix file, as it contains private health information in the data tables, and I'm afraid creating a dummy file would not properly recreate the environment I'm having the issue in.   Hopefully my answers and pictures are enough to further the discussion.   Thanks in advance!

Microsoft v-jiascu-msft
Microsoft

Re: Rebuilding report - different results with SAMEPERIODLASTYEAR measure... why?

Hi @MTracy,

 

Please make a little change. Don't create a relationship using the Datekey, use the Date directly (the Date type columns). This could be the workaround. I will find out the root cause later and share it here.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MTracy Frequent Visitor
Frequent Visitor

Re: Rebuilding report - different results with SAMEPERIODLASTYEAR measure... why?

Hi, @v-jiascu-msft - thanks for replying.

 

Could that really be the issue?  I only ask because on the first pbix (where this whole set of DAX calculations works properly) the relationship is set between the Datekey columns in each table, which read "Whole Number" in the Data Type formatting section.  If it works in the first pbix, then logic says the same thing should work in the rebuild with the same "Whole Number" setting... right?  Am I missing something?   Thanks in advance for any help!

Microsoft v-jiascu-msft
Microsoft

Re: Rebuilding report - different results with SAMEPERIODLASTYEAR measure... why?

Hi @MTracy,

 

The datekey should work. But it messed up in my test. Did changing the relationship solve your issue? If it can work, we can take it as a workaround for now. I'm still trying to find out the root cause.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft v-jiascu-msft
Microsoft

Re: Rebuilding report - different results with SAMEPERIODLASTYEAR measure... why?

Hi @MTracy,

 

Did it work when you had the relationship between date columns.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MTracy Frequent Visitor
Frequent Visitor

Re: Rebuilding report - different results with SAMEPERIODLASTYEAR measure... why?

Hi @v-jiascu-msft, thanks for keeping up on this!

I have had something unusual happen.  When I loaded the rebuild pbix file (the new one that didn't work like the old one), I suddenly noticed the calculations were working!  I didn't touch the DAX formulas, the relationships, or anything.   There was a refresh of the underlying SQL Server data, and some other DAX calculated columns added for unrelated reporting... that's it.   No relationship changes, it simply started to work.

 

YoY solved..PNG

 

I'm not entirely sure I can still call this "broken".   I do have to say that the sudden change is quite a mystery.   I can't think of what would have changed this.   I did install the November update in the middle of all this, so maybe that had something to do with it?   Thoughts anyone?

 

Microsoft v-jiascu-msft
Microsoft

Re: Rebuilding report - different results with SAMEPERIODLASTYEAR measure... why?

Hi @MTracy,

 

I tried to find out the mode of this issue. But it is intermittent. So I asked you about this. I'm glad you can go on now. I will appreciate it if you can close this thread. You can post a new one anytime you encounter an issue.

 

Best Regards,

Dale

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors