Forum Replies Created
-
AuthorPosts
-
Here is what I used. I think I was making it too complicated.
Partial Prior Year = IF(YEAR(MAX([OrderDate]) = YEAR(TODAY())),
CALCULATE([Last Year Booked], FILTER(DimDate, [Is Before Today]=1)), CALCULATE([Last Year Booked], ALL(DimDate[Is Before Today])))
</div>Tom,
It is a beautiful work of art that refreshes quickly. It also works well in the YoY attrition calculation.
Your help is appreciated and I always enjoy working with you.
Kristi
Tom,
The three columns and the measure are working appropriately. It is returning slightly different numbers than the original version we tried that worked. The old one was 14,714 and the new one is 14,798.
I am very interested in your combined version as I am finding this very educational.
One other question. Have you read the book “If you give a mouse a cookie”? I only ask because now my team would like to see how this changes if we step back in time on a monthly basis. So where our original calculation was 0-12 or 12-24, what about 13-25, 14-26 . . .
I am almost positive that Rob warned me this would happen.
Thank you for your help Tom. The working calculation has appeased the Powers that be. I am working on cleaning up both the fact table and the look up table with the hope of revisiting your last suggestion. I will update you with that progress soon.
Tom,
I have attempted this last calculation Unfortunately, my PowerBI just churns and never really finishes the if statement. It will either max out my CPU, or my Disk from the Microsoft SQL Server Analysis Services process. I have had to resort to using End Task to get my computer to respond after that.
Current rendition shows 793,633 rows on the customer table.
My bad, The freshness date was a measure and was iterating for each row of the table. I changed the way it was calculated and now the measures are performing as they should. I currently whos Customers Active — Last 12 and Prev 12 as being equal to 14,707 with no filtering or slicers.
Tom,
I seem to be returning a result of Blank. My freshness date shows as 09/27/2017 but when I visit the sales performance table every row shows “Lst 12 Mo” with none showing as “Prv 12 Mo”. Even the lines with order dates of Jan 1, 2013.
Here is a snip of my data model.
Attachments:
You must be logged in to view attached files.Tom,
It is always a pleasure to work with you.
I can use the freshness date, I have that calculation already in the report to show the last refresh as well. I could also use the dates between 0-364 and 365-629.
You hit the nail on the head with the customer table. There are customers who bought many years ago. so many years, in fact, that our fact table doesn’t contain the data. There are potential customers who have never purchased. There are also failed attempts at entering customer information in resulting in customers named “.”, “\”, or “10-inch fry Pan”. And duplicates . . . oh the duplicates! We have multiple entries per company per rep and multiple heinous errors such as differentiating McDonalds from mcdonalds from MacDonalds due to data entry processes that have been/are being addressed.
I clean as much up as I can in Power Query but the bloat really kills performance. If the customer calculations can be built in the fact table, it will perform much better. The customer table (and the UPS file) are the bane of my existence and neither can really be repaired at the source at this time. Any guidance you could offer would be greatly appreciated.
Tom,
I don’t know why my posts keep disappearing. I thought perhaps they were awaiting moderation but this is the third one in a week.
thank you for your suggestion. I will start there and see where it leads.
Thank you!
Kristi
August 17, 2016 at 2:34 pm in reply to: combine a row in Power Query without grouping and losing other data. #5600Thank you. We will call that solved and we shall also call it lesson learned. I had no idea that group could return multiple columns. I had simply never considered it.
Learn something everyday.
Trying again to attach the file. Here is a dropbox link.
https://www.dropbox.com/s/x9cul41v8ck9h6b/test%20data%202.0.xlsx?dl=0
Solved it with the card. While stepping through the measure I realized that this part: [% Cumulative Total] was referencing a different category. Therefore, I was multiplying by zero which Siri will tell you is futile. I did discover it by using your method of adding the measure back to the visible data. Thank you for your help and emotional support.
Clarification: They do not break, they go blank. And, the merged table is from a lookup set and one of the fact tables. The merge was unnecessary and I would prefer to use the original fact table. However, when I connect the relationships, the numbers disappear. I am assuming, at this point, that my calculation has an error because when I rewrite, it is still blank.
Here is the measure:
CALCULATE([Total Campaign Results],
FILTER(‘Merged campaign information’,
‘Merged campaign information'[Vendor]=”Vendor”||
‘Merged campaign information'[Vendor]=””||
‘Merged campaign information'[Vendor]=”Keyword”||
‘Merged campaign information'[Vendor]=”Branding”||
‘Merged campaign information'[Vendor]=”–“||
‘Merged campaign information'[Vendor]=”Competitor”||
‘Merged campaign information'[Vendor]=”Google Shopping”)
) *
[% Cumulative Total]
Pulling out the card to step through it now.
-
AuthorPosts