Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Cumulative sum for dates with no values

Hi guys,

 

I have to achieve this graph, where on the 

DatesImportsCumulative imports
6th Sept6666
29th Sept67133


I am currently using the formula:

 

 

Cumulative = CALCULATE(SUM(LNG_Kpler_ImportsExtract[QTY_Destination]), FILTER(ALLEXCEPT(LNG_Kpler_ImportsExtract,LNG_Kpler_ImportsExtract[Country_destination]), LNG_Kpler_ImportsExtract[Date_destination] <= MAX(LNG_Kpler_ImportsExtract[Date_destination])))

 

 

However, I am getting a graph like that 

 

 

How do I get the cumulative for days in which I dont have data for? 

 

Cumulative = CALCULATE(SUM(LNG_Kpler_ImportsExtract[QTY_Destination]) + 0 , FILTER(ALLEXCEPT(LNG_Kpler_ImportsExtract,LNG_Kpler_ImportsExtract[Country_destination]), LNG_Kpler_ImportsExtract[Date_destination] <= MAX(LNG_Kpler_ImportsExtract[Date_destination])))

 

 

I tried adding a zero which creates values for individual days but I have no idea how to proceed. 

Need Help

1 Reply

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

    Anonymous , Your data and formula are not matching

    Cumulative = CALCULATE(SUM(LNG_Kpler_ImportsExtract[QTY_Destination]), FILTER(allselected(LNG_Kpler_ImportsExtract), LNG_Kpler_ImportsExtract[Date_destination] <= MAX(LNG_Kpler_ImportsExtract[Date_destination])))

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.