Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Running total (with blank value in between weeks) ?

I have a
Running total = CALCULATE(SUM(data_SAP_OrderQTY[Billing Quantity SU]), FILTER(all(data_SAP_OrderQTY),data_SAP_OrderQTY[Order Date]<=MAX(data_SAP_OrderQTY[Order Date]))), 
 
%Used = #Deviced Used / Running total. 
 
The problem is when i chart it in a line ( right graph). the weeks dont have sales, it iwll not have a data point. How do i fix this? 
For example, on 2020 week 9, there's 1 deviced sold, and running total become 94+1 = 95, but theres not sales till week 14. And i would like to use 95 for thoes week . 
 
THANK YOU!!!
 

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , You should use date table

     

    Running total = CALCULATE(SUM(data_SAP_OrderQTY[Billing Quantity SU]), FILTER(all(Date),Date[ Date]<=MAX(Date[Date])))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , You should use date table

     

    Running total = CALCULATE(SUM(data_SAP_OrderQTY[Billing Quantity SU]), FILTER(all(Date),Date[ Date]<=MAX(Date[Date])))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.