Forum Discussion

LS1700's avatar
LS1700
Resolver I
9 years ago
Solved

Running total and null values

Hello,   I have some different tables with sales and I am building a new table with a coulmn where I entered the days of the month (1-31). I managed to add measures to this table with the units so...
Need Help
  • LS1700's avatar
    LS1700
    9 years ago

    I found the solution!

     

    Wrong: MTD Invoiced = CALCULATE(SUM(Invoicing[Qty]),FILTER(ALLSELECTED(Invoicing),Invoicing[Day]<=MAX(Invoicing[Day])))

     

    Correct: MTD Invoiced = CALCULATE([Invoiced Qty],filter(ALLSELECTED('Table for Chart'),'Table for Chart'[Day Index]<=max('Table for Chart'[Day Index])))

     

    As you can see, the second is referencedto the new table I created. now it show each time the MTD.

LS1700's avatar
LS1700
Resolver I
9 years ago

Hey,

 

thanks for your answer.

 

Show items with no data is already selected.

 

I cannot reference to the Date because I intentionally want to look at the day of the month. I want to compare the results with other months or other data (such as open orders...).

 

I appended to the invoice table 31 lines, so that I have all days of the month. Now it is a bit better, but as soon as I filter out (E.g. I select a specific area) I have null values.

 

The "+0" did not work either. it creates 0 values.

 

Any other suggestion?

 

thanks

LS

 

 

LS1700's avatar
LS1700
Resolver I
9 years ago

I found the solution!

 

Wrong: MTD Invoiced = CALCULATE(SUM(Invoicing[Qty]),FILTER(ALLSELECTED(Invoicing),Invoicing[Day]<=MAX(Invoicing[Day])))

 

Correct: MTD Invoiced = CALCULATE([Invoiced Qty],filter(ALLSELECTED('Table for Chart'),'Table for Chart'[Day Index]<=max('Table for Chart'[Day Index])))

 

As you can see, the second is referencedto the new table I created. now it show each time the MTD.