cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkS Member
Member

Re: create DAX expression that includes outer members of an inner join

So using this formula WorkDaysInPeriodUntilToday = CALCULATE(COUNT('Calendar'[date]),FILTER('Calendar','Calendar'[work_day_flag]="Yes" && 'Calendar'[date]<=TODAY()))

 

should get what you need.  This screenshot shows the monthly revenue per workday and an overall total for the months selected in the slicer.

viz 02152017-1.PNG

 

and if only January was selected in the slicer you would get this

viz 02152017-2.PNG

 

 

 

 

MarkS Member
Member

Re: create DAX expression that includes outer members of an inner join

There are also measures that you can define that will calculate to the totals that are shown in the Matrix using the ALLSELECTED function.

 

Total Workdays2 = CALCULATE(COUNT('Calendar'[date]),FILTER(ALLSELECTED('Calendar'),'Calendar'[work_day_flag]="Yes" && 'Calendar'[date]<=TODAY()))   --33

 

use a similar ALLSELECTED function on the revenue calculation Revenue2=CALCULATE(SUM(Order_Fact_Table[Amount]),ALLSELECTED('Calendar'))  --36,085.42

 

Revenue Per Day2 = Divide(Revenue2,Total Workdays2)  --1093.50

 

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 36 members 944 guests
Please welcome our newest community members: