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.

Sean's avatar
Sean
Community Champion
9 years ago

Couple things you can try

 

1) Go to the Date filed in the Values area of the Table Visual => click the down arrow => select Show Items with No Data

 

2) Reference the Date column from your Calendar Table instead

 

MTD Invoiced =
CALCULATE (
    SUM ( Invoicing[Qty] ),
    FILTER (
        ALLSELECTED ( CalendarTable ),
        CalendarTable[Date] <= MAX ( CalendarTable[Date] )
    )
)
    + 0

The + 0 is optional but it will give you basically leading zeros if your Calendar Starts prior to any transactions and you want those dates to show 0.

For example first few days of a Year selected in a Slicer!

 

 

Hope this helps! :smileyhappy:

  • 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.