Adding a purchase number field into a table

D

Dan Yuncken

New Member
Joined
Nov 24, 2013
Messages
3
Hi everyone,


I have a DAX question I’m having trouble finding an answer for in this forum. I’ve found some good answers on cumulative totals but nothing that will work in the context I need it in.



I have a sales table linked to three dimension tables: date table (Sale Table Key: Invoice Date), customer table (Sale Table Key: Outlet Number) and product table (Sale Table Key: Product number).



I need a table with two calculated fields ('Sum of Actual Cases' &'Purchase Number') as follows:

[TABLE="width: 415"]
<tbody>[TR]
[TD="width: 132"]Invoice Date
[/TD]
[TD="width: 108"]Product Number[/TD]
[TD="width: 96"]Outlet Number[/TD]
[TD="width: 124"]Sum of Actual Cases[/TD]
[TD="width: 95"]Purchase Number[/TD]
[/TR]
[TR]
[TD="width: 132"]Feb 10, 20014
[/TD]
[TD="width: 108"]Product 1[/TD]
[TD="width: 96"]Outlet 1[/TD]
[TD="width: 124"]
5​
[/TD]
[TD="width: 95"]
1​
[/TD]
[/TR]
[TR]
[TD="width: 132, bgcolor: transparent"]Feb 10, 20014
[/TD]
[TD="width: 108, bgcolor: transparent"]Product 2[/TD]
[TD="width: 96, bgcolor: transparent"]Outlet 1[/TD]
[TD="width: 124, bgcolor: transparent"]
3​
[/TD]
[TD="width: 95, bgcolor: transparent"]
1​
[/TD]
[/TR]
[TR]
[TD="width: 132"]Feb 10, 20014
[/TD]
[TD="width: 108"]Product 1[/TD]
[TD="width: 96"]Outlet 2[/TD]
[TD="width: 124"]
4​
[/TD]
[TD="width: 95"]
1​
[/TD]
[/TR]
[TR]
[TD="width: 132, bgcolor: transparent"]Feb 11, 20014
[/TD]
[TD="width: 108, bgcolor: transparent"]Product 3[/TD]
[TD="width: 96, bgcolor: transparent"]Outlet 1[/TD]
[TD="width: 124, bgcolor: transparent"]
3​
[/TD]
[TD="width: 95, bgcolor: transparent"]
1​
[/TD]
[/TR]
[TR]
[TD="width: 132"]Feb 17, 20014[/TD]
[TD="width: 108"]Product 2[/TD]
[TD="width: 96"]Outlet 1[/TD]
[TD="width: 124"]
2​
[/TD]
[TD="width: 95"]
2​
[/TD]
[/TR]
[TR]
[TD="width: 132, bgcolor: transparent"]Feb 17, 20014
[/TD]
[TD="width: 108, bgcolor: transparent"]Product 2[/TD]
[TD="width: 96, bgcolor: transparent"]Outlet 2[/TD]
[TD="width: 124, bgcolor: transparent"]
3​
[/TD]
[TD="width: 95, bgcolor: transparent"]
1​
[/TD]
[/TR]
[TR]
[TD="width: 132"]Feb 19, 20014
[/TD]
[TD="width: 108"]Product 2[/TD]
[TD="width: 96"]Outlet 1[/TD]
[TD="width: 124"]
3​
[/TD]
[TD="width: 95"]
3​
[/TD]
[/TR]
</tbody>[/TABLE]


I’m having trouble with the ‘Purchase number’ calculated field . It is the chronological number of times an outlet has purchased a particular product.



I tried adding
:=CALCULATE (
DISTINCTCOUNT('Sales Table'[Invoice Date]),

FILTER (

ALL ( 'Date Table'),
'Date Table'[Date] <= MAX ('Date Table'[Date])

)

)


But the resulting output is always 1 – I think the ‘Sales Table’[Invoice date] is limiting the date range. When I replace ‘Sales Table’[Invoice date] with 'Date Table'[Date] field it works fine but it populates a table with all the dates – including those dates without sales.



Any help here would be fantastic


Dan
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Your measure it likely how I would have written it as well. What do you have "on rows" of your pivot table? Try the column from the Date Table instead of from the Sales table?
 
Upvote 0
scottsen said:
Your measure it likely how I would have written it as well. What do you have "on rows" of your pivot table? Try the column from the Date Table instead of from the Sales table?
Click to expand...


When I put the ‘Date Table’[Date] field into table instead of the ‘Sales Table’[Invoice Date] it populates a table containing dates without sales.<o:p></o:p>
See below<o:p></o:p>
*required calculated field

<o:p></o:p>
[TABLE="class: MsoNormalTable, width: 392"]
<tbody>[TR]
[TD="width: 136"] Sales Table'[Invoice Date]<o:p></o:p>[/TD]
[TD="width: 93"] Product Number<o:p></o:p>[/TD]
[TD="width: 80"] Outlet Number<o:p></o:p>[/TD]
[TD="width: 107"] Sum of Actual Cases<o:p></o:p>[/TD]
[TD="width: 107"] Purchase Number*<o:p></o:p>[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 10, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"]
5<o:p></o:p>​
[/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 10, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"]
4<o:p></o:p>​
[/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 10, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"]
3<o:p></o:p>​
[/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 11, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 3<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"]
3<o:p></o:p>​
[/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 17, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"]
2<o:p></o:p>​
[/TD]
[TD="width: 107"]
2<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 17, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"]
3<o:p></o:p>​
[/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 19, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"]
3<o:p></o:p>​
[/TD]
[TD="width: 107"]
3<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"][/TD]
[TD="width: 93"][/TD]
[TD="width: 80"][/TD]
[TD="width: 107"][/TD]
[TD="width: 107"][/TD]
[/TR]
[TR]
[TD="width: 136"] Changes To:<o:p></o:p>[/TD]
[TD="width: 93"][/TD]
[TD="width: 80"][/TD]
[TD="width: 107"][/TD]
[TD="width: 107"][/TD]
[/TR]
[TR]
[TD="width: 136"] ‘Date Table’[Date]<o:p></o:p>[/TD]
[TD="width: 93"] Product Number<o:p></o:p>[/TD]
[TD="width: 80"] Outlet Number<o:p></o:p>[/TD]
[TD="width: 107"] Sum of Actual Cases<o:p></o:p>[/TD]
[TD="width: 107"] Purchase Number<o:p></o:p>[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 10, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"]
5<o:p></o:p>​
[/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 10, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"]
4<o:p></o:p>​
[/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 10, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"]
3<o:p></o:p>​
[/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 11, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 11, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 11, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 11, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 3<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"]
3<o:p></o:p>​
[/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 12, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 12, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 12, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 12, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 3<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 13, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 13, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 13, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 13, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 3<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 14, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 14, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 14, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 14, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 3<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 15, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 15, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 15, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 15, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 3<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 16, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 16, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 16, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 16, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 3<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 17, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 17, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 17, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"]
2<o:p></o:p>​
[/TD]
[TD="width: 107"]
2<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 17, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"]
3<o:p></o:p>​
[/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 17, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 3<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 18, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 18, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 18, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
2<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 18, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 18, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 3<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 19, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 19, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 1<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 19, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"]
3<o:p></o:p>​
[/TD]
[TD="width: 107"]
3<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 19, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 2<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 2<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
[TR]
[TD="width: 136"] Feb 19, 20014<o:p></o:p>[/TD]
[TD="width: 93"] Product 3<o:p></o:p>[/TD]
[TD="width: 80"] Outlet 1<o:p></o:p>[/TD]
[TD="width: 107"][/TD]
[TD="width: 107"]
1<o:p></o:p>​
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Yep. By default, excel hides all rows that have ALL blanks values. Since Purchase # isn't coming back blank... you see the row.

Can you re-write your Purchase # measure to return BLANK()? Perhaps IF (ISBLANK([Sum of Actual Cases]), BLANK(), <your purchase# logic>) ?
 
Upvote 0
scottsen said:
Yep. By default, excel hides all rows that have ALL blanks values. Since Purchase # isn't coming back blank... you see the row.

Can you re-write your Purchase # measure to return BLANK()? Perhaps IF (ISBLANK([Sum of Actual Cases]), BLANK(), <your purchase#="" logic="">) ?
Click to expand...

Brilliant! Nice and simple. Thanks.
I was thinking the solution was going to involve a SUMX or the like on the sales table. Appreciate your lateral thinking here!

For the record:
:=IF(
ISBLANK(​
SUM('Sales Table'[Actual Cases])​
),​
BLANK(),
CALCULATE(​
DISTINCTCOUNT('Sales Table'[Invoice Date]),
FILTER(​
ALL('Date Table'),
'Date Table'[Date]<=MAX('Date Table'[Date])​
)​
)​
)

Thanks again,
Dan
</your>
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
DAX: Double or Nested COUNTX() & SUMX()
Replies
6
Views
531
luuk
L
P
  • Question Question
DAX PIVOT: Projects Closure Date Field - Number of Days Change - between differing Forecast Versions
Replies
0
Views
398
phil134
P
D
  • Solved
Combine information from one table to another? (Power query or formulas?)
Replies
1
Views
219
danhendo888
D
A
  • Solved
adding extra lines to query removed all steps
Replies
8
Views
378
RoryA
RoryA
RICH937
  • Question Question
How to create Running Totals on multiple non-date values and conduct filtered analysis on the results. Which Power Tool is the more efficient choice?
Replies
3
Views
441
RICH937
RICH937

Forum statistics

Threads
1,223,997
Messages
6,175,874
Members
452,679
Latest member
darryl47nopra

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top