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
LenaS
New Member

QTD and YTD DAX formulas - data results in pivot

I have QTD and YTD formulas in my power pivot. Oe of the data examples is revenue per doctor.

When I pivot the data and select, say, October as a month to show me MTD, QTD and YTD results, it shows me only the doctors active in October. It beautifully calclulates their QTD and YTD revenue, but omits the dcotors that were active in prior months, e.g docs who quit. 

 

I need to show ALL the doctors, regardless of whether they had revenue in this particular month.

 

How do I do that?

 

This is my formula:

MOS is my date field.

YTD_OperRevenue:=TOTALYTD(SUM(MORTbl[Oper Revenue]),MORTbl[MOS], ALL(MORTbl[MOS]))

 

Thank you!

 

Lena

1 ACCEPTED SOLUTION

I suspect the issue is that you don't have a calendar table. Your data table should only contain the date, the month filter should come from the calendar table. Read up here. 

 

http://exceleratorbi.com.au/power-pivot-calendar-tables/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

2 REPLIES 2

I suspect the issue is that you don't have a calendar table. Your data table should only contain the date, the month filter should come from the calendar table. Read up here. 

 

http://exceleratorbi.com.au/power-pivot-calendar-tables/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Matt, 

You are right - I do not have a calendar table! It makes sense. I have a strong feeling this will solve my problem.

Thanks!

Lena

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.