Forum Discussion

VTur's avatar
VTur
Frequent Visitor
3 years ago

Running total in graph with blank/missing dates

Hi guys,

I've read lots about on this forum about how to andle blanks/missing values in running totals but none helped me in solving my problem (I must point out that these are my firsts months on PowerBI).

 

I'll be short; I have 2 tables:

- Calendar Table: where i collect alla the dates from the lower date of tbale MOVIMENTI to the higher

- MOVIMENTI: it's my main dataset, wich contain values like "DATA" (date in italian), "IMPIANTO" (plant) and TIPO MOVIMENTO (type of action).

 

What I'd like to do is create a runnning total for the measure where I count TIPO MOVIMENTO each day for each plant in order to plot it into a line chart.

Here's where I am now:

As you can see there are days where users don't interact, thus COUNT returns empty/blank and my line chart presente these ugly interrumptions.

 

What can I do?

 

Thanks in advance for your support and keep on helping people like me!πŸ’ͺ

 

 

 

PS: these are the formulas I've written in my measures:

Total Tipo movimentoCOUNT(MOVIMENTI[Tipo movimento])

Cumulative Total Tipo MovimentoCALCULATE([Total Tipo movimento]FILTER(ALL('Calendar Table'), 'Calendar Table'[Data] <= MAX('Calendar Table'[Data])))

 

NB: I want to count only one kind of action in TIPO MOVIMENTO, "Cambio tipologia card", but i imagine that isn't a problme beacuase i have filtered the page.

8 Replies

  • Hi,

    Revise your measure to:

    Cumulative Total Tipo Movimento = CALCULATE([Total Tipo movimento],DATESBETWEEN('Calendar Table'[Data],MINX(ALL('Calendar Table'),'Calendar Table'[Data]),MAX('Calendar Table'[Data])))

    Ensure that on the X-axis of the visual, you drag Month/Year from the Calendat Table. 

    • VTur's avatar
      VTur
      Frequent Visitor

      HiAshish,

      unfortunatly that's the output:

       

      It gives back the total for each day and other errors.

       

      Here's the CSV of the outputs in case you need (WeTransfer: https://we.tl/t-a8qPgjJaE6) :

      - "Your formula" contains the result of your formula in the graph

      - "Previous formula" contains the result of my formula in the graph, the empty cells are the ones i want to fill

       

       

      Thank you for your support!