Forum Discussion
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 movimento = COUNT(MOVIMENTI[Tipo movimento])
- Cumulative Total Tipo Movimento = CALCULATE([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
- Ashish_MathurSuper User
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.
- VTurFrequent 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!
- Ashish_MathurSuper User
Hi,
Share the download link of the PBI file.