Total Accumulated without data

[locle90]

Dear All,

I have fomule to calculate the count of item with leadtime days 0-14.But some days we don't have the order then  i need day 7,8,9 should show with data 79.

Could you help to advice how i can change my DAX ?

[PurpleGate · Accepted solution]

As Lead time is 0-14 days,  I made a raw table called OrderLeadTime just 15 rows with the numbers 0-14

Then I linked as a 1-* to your calculated column "Order Lead Time" in table LeadTime

xx cumul = 
var c = max(OrderLeadTime[OLT_Raw])
var t = CALCULATE(DISTINCTCOUNT(Leadtime[Item]),REMOVEFILTERS(OrderLeadTime[OLT_Raw]))
var td = CALCULATE(DISTINCTCOUNT(Leadtime[Item]),OrderLeadTime[OLT_Raw]<=c)
return divide(td,t,1) 

[PurpleGate]

I see. If your column is a measure, and you cannot use SUM then how about this:

Wish Result = CALCULATE([insert your count measure here],FILTER(ALL('Leadtime'[OLT]),'Leadtime'[OLT] <= MAX ('Leadtime'[OLT])))

[locle90]

Dear PurpleGate,

Thanks for your strongly support but my result still same with my expactation .

[PurpleGate]

try removing "distinct count" from your measure. 

If this is referring to a column, then you can use sum, and if it is referring to a measure, you can just write the measure name. 

[locle90]

Dear,

This is my DAX and my wish result.

[PurpleGate]

How is your data set up?

My "count" column is put in as a sum

I think you might need to create a measure for your "Count of item" column and then refer to that.

Try:

Count of item = CALCULATE(SUM('Table'[Count]),FILTER('Table','Table'[OLT]))

and then do

VN OLT 2 = CALCULATE([Count of item],FILTER(ALLSELECTED('Table'[OLT]),'Table'[OLT]<=MAX('Table'[OLT])))

In my column called "Measure", the formula is saying

sum up the [count] column in order of the [OLT] column

Measure = CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'[OLT]),'Table'[OLT]<=MAX('Table'[OLT])))

In my column called [vn olt], the formula is saying

if there is data in the [count] column, sum up the [olt] column, in the order of [olt] column

VN OLT = CALCULATE(DISTINCTCOUNT('Table'[Count]),FILTER(ALLSELECTED('Table'[OLT]),'Table'[OLT]<=MAX('Table'[OLT])))

[locle90]

Dear PurpleGate,

I found a different that my count of item :

Count of Item = DISTINCTCOUNT(Leadtime[Item])

I need to count the order and show that on the chart not SUM.

So i still don't know how to fix it 

PREVIEW

[PurpleGate]

From what I understand, you want to see a rolling total?

If days 7, 8 and 9 should all show the value 79?

In that case, SUM would be best as it is adding up all the numbers prior to the current row.

If that isnt correct, perhaps you could write in the numbers you expect to see and then we can figure out a solution to get them? 

[PurpleGate]

Hi,

Try this measure

Measure = CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'[OLT]),'Table'[OLT]<=MAX('Table'[OLT])))

[locle90]

Dear PurpleGate,

My DAX count mean : " count of item on data list". So i can use the Sum fomula like you.

Could you refe DAX bellow and give your adivice.

VN OLT = CALCULATE (

    DISTINCTCOUNT(Leadtime[Item]),

    FILTER (

        ALLSELECTED(Leadtime),

        Leadtime[OLT] <= MAX( Leadtime[OLT])

))

[PurpleGate]

Hi Locle90,

When I use that formula, it works to count the rows with data. I have 11 rows in total and 8 of them have data in them in the "count" column. 

I put below the result. It works well, if this is what you are after!

VN OLT = CALCULATE(DISTINCTCOUNT('Table'[Count]),FILTER(ALLSELECTED('Table'[OLT]),'Table'[OLT]<=MAX('Table'[OLT]))) 

[locle90]

Dear PurpleGate,

 I tried with your DAX but the result still same.Could you help to share the fomula of measure ?