Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

YTD Calculation on Certain Rows Only

Hi All,

This is my first post so apologies if I don't have the right format or etiquette 🙂 I am quite new to PBI but would like to think I am learning fast. I'm trying to calculate a YTD sales measure for a staff ID number but only based on certain fields. So in the screenshot below what I would want to calculate is a YTD SALES column (green) but only where the other column entries all match. For example, the rows highlighted in yellow would calculate to YTD, whereas those highlighted in orange won't because the state / city combination is different even though it is the same STAFF ID. T

I have tried various versions of this post but can't seem to make it do what I want 😞 

 

Solved: How to do cummulative sum based on group and date(... - Microsoft Power BI Community

 

Any help would be greatly appreciated!

 

Ian

 

ian_sheerin_0-1617292150146.png

 

1 REPLY 1
daxer-almighty
Solution Sage
Solution Sage

Hi @Anonymous 

 

First, I can't even figure out whether you need a measure or a calculated column. If you need a measure, then you have to model your data in the right way in the first place. Good data modeling is crucial to any good - AND FAST - model. All the rows that qualify for your calculation should be marked with 1 (or True) and the other ones with 0 (False). You need a new column for this and if this is your fact table (as I think it is), it should always be hidden. Yes, all columns in fact tables should be hidden in a well-designed model. Once you've got the column, it's then easy to create the measure you want. VERY EASY in fact. The column I'm talking about should be created in Power Query, not in DAX.

 

By the way, if you want to really learn "fast" and in the right way, you should do yourself a favour and read THIS.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors