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
brian123
Frequent Visitor

Using DatesBetween as a Filter - Subtotal not populating on card, but works on table visual

Hi All, 

 

I am automating our salespeople's commission process and have gotten stuck. Hope you all can help! 

 

This person's contract states that in addition to regular commission, they also get 5% commission on new accounts for the first 120 days after that account's initial order (first sales date). There are certain qualitative criteria that must be met in order to qualify for new account commission, so I will simply tag the account number on the customer table with the person who will receive the new account commission on it. I have a field on the customer table called "New Biz Owner", which I manually populate. I also have measures for [First Sales Date] and [First Sales Date + 120 Days]. 

 

My measure for First Sales Date... 

 

First Sales Date = calculate(FIRSTDATE('Sales Document Header'[Posting Date]),FILTER('Sales Document Line','Sales Document Line'[Sales Amount]>0))

 

and FSD+120..

 

FSD + 120 = [First Sales Date]+120

 

To calculate Total Sales from accounts tagged with a specific New Biz Owner (in this case Shane), I simply have:

 

Shane New Biz Sales = CALCULATE([Posted Sales],'Intl Customers'[New Biz Owner]="Shane")

 

And to further filter that by only the first 120 days after each account's respective start date:

 

Shane New Biz 120 = calculate([Shane New Biz Sales],DATESBETWEEN('Posting Date'[Date],[First Sales Date],[FSD + 120]))

 

newbiz120.JPG


All of the measures work, which I have verified in this table above. The Shane New Biz sales calculate and subtotal at $239k, and when I add the DATESBETWEEN filter on Shane New Biz 120, it correctly totals the individual account row in the correct timeframes (each account has a different start date), however, as you can see, there is no subtotal for Shane New Biz 120, which would be the commissionable amount. This causes problems, because if I try to put Shane New Biz 120 onto a card visual, or use in another measure, it shows as (blank)...

 

newbiz120card.JPG

 

I feel like there is some fundamental error in my programming. The next thing I am going to try is creating a calculated column on the customer table for total sales, and then filtering the dates on that into another calculated column, but there has to be an easier way. 

 

Appreciate any feedback, thanks!!

 

-Brian

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Try this measure

=SUMX(SUMMARIZE(VALUES(Data[Customer #]),Customer #,"ABCD",[Shane New Biz 120]),[ABCD])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

Try this measure

=SUMX(SUMMARIZE(VALUES(Data[Customer #]),Customer #,"ABCD",[Shane New Biz 120]),[ABCD])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

When you use a measure, where row context plays a role. Like here the customer of the row will let you know the dates. You need to preserve the same for the grant total

Example

sumx(summarize(table, table[customer #],"_newbiz",calculate([Shane New Biz Sales],DATESBETWEEN('Posting Date'[Date],[First Sales Date],[FSD + 120])),[_newbiz])

 

You might have to do small changes to make it work.

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

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.