Filling the blanks of a running total with last non-blank value

[MrClod]

I have a measure that I have created against a separate table to have a running total of created tickets by date.  When no tickets have been created on a particular date, I want to repeat the previous non-blank value.

For example, on 8 April, no tickets were created, so I want my total to show 389, just like the previous day.

My measure is calculated with this snippet:

#totalCreated = IF(CALCULATE(COUNT(ticketData[CreateDate]),
	                     FILTER(ALL(ticketData),
				    ticketData[CreateDate] <= MAX(ticketData[CreateDate]))),
		   CALCULATE(COUNT(ticketData[CreateDate]),
	                     FILTER(ALL(ticketData),
				    ticketData[CreateDate] <= MAX(ticketData[CreateDate]))), 
		   0    // some change here to get desired result
)

I have tried LASTNONBLANK, but for dates, it wants non duplicates, while the ticketData table can have multiple tickets created in a day.  I am adding this measure to a a calendar table that holds all the values from my intended date range since I cannot have gaps in my dates  either.

      CALENDAR(MINX('ticketData', ticketData[CreateDate]), NOW())

[emremehmet]

Hi,

I do have pretty much same problem, tried to fix it with you methodology but it didnt work for me. 

could you help me about it please;

here is my dax query for running total count

Count of ADMISSIONOID running total in RowNumber =

CALCULATE(
DISTINCTCOUNT('vw_SIS_STUDENT_ENROLLMENT'[ADMISSIONOID]);
FILTER(
ALLSELECTED('vw_SIS_STUDENT_ENROLLMENT'[RowNumber]);
ISONORAFTER('vw_SIS_STUDENT_ENROLLMENT'[RowNumber]; MAX('vw_SIS_STUDENT_ENROLLMENT'[RowNumber]); DESC)
)
)

[rusgesig]

I'm sitting here two years later with exactly the same problem. The issue with the solution presented is that it only works on a calendar table, if you are not working with dates but rather a sequence no. or number defining a period of time it does not work.

This little problem has become the bane of my existence.

[evostic]

I'm here another 2 years later and feeling your pain! Is there a solution for a non Date table scenario? Self-help group ? 🙂

[sam_gift]

Same issue with nearly 2 more years 😞  Screaming for help!

[emremehmet]

Hi everyone, I need quick help, I know there is another message with same subject but I have another trick in my table.

first, I have increment numbers instead dates, (like 1-2-3-4-5-...18), I do replace date column with my "row number" column. and want to fill empty rows with the last number of rownumber. 
this is my dax query for running total;

Count of ADMISSIONOID running total in RowNumber =
CALCULATE(
DISTINCTCOUNT('vw_SIS_STUDENT_ENROLLMENT'[ADMISSIONOID]);
FILTER(
ALLSELECTED('vw_SIS_STUDENT_ENROLLMENT'[RowNumber]);
ISONORAFTER('vw_SIS_STUDENT_ENROLLMENT'[RowNumber]; MAX('vw_SIS_STUDENT_ENROLLMENT'[RowNumber]); DESC)
)
)

[v-jiascu-msft]

Hi @MrClod,

It seems that you want to sum up the tickets from the start date to the current date. The IF couldn't be necessary. We can sum the column "created".

#TotalCreated =
CALCULATE (
    SUM ( ticketData[Created] ),
    FILTER (
        ALL ( ticketData ),
        ticketData[CreateDate] <= MAX ( ticketData[CreateDate] )
    )
)

[MrClod]

I should have named that created column to #created since it's a measure that calculates the # of created tickets on a day from the TicketData table and exists in its own table.

SUM does not appear to want to deal with this column, nor will SUM allow me to include the definition of #created.

[v-jiascu-msft]

Hi @MrClod,

A measure can be used in another measure. You can have a try. Maybe your formula looks like this:

#TotalCreated =
CALCULATE (
    [Created],
    FILTER (
        ALL ( ticketData ),
        ticketData[CreateDate] <= MAX ( ticketData[CreateDate] )
    )
)

[MrClod]

I would think so as well, but I still have 0 for those days that do not have any tickets created.

[v-jiascu-msft · Accepted solution]

Hi, @MrClod

I have reproduced your issue and I found out the cause. The column "Date" in the report isn't from the table "ticketData" , is this right? If it's the truth, you can fix this by these steps.

1. Make sure these two tables have relationships.

2. Change the formula into this: (the column where the column 'date' in the report belongs, maybe a little change needed)

#totalcreated =
CALCULATE (
    COUNT ( ticketData[CreateDate] ),
    FILTER (
        ALL ( ticketData ),
        ticketData[CreateDate] <= MAX ('Date'[date] )
    )
)

Please try this. 

 

[emremehmet]

I have another trick in my table.

first, I have increment numbers instead dates, (like 1-2-3-4-5-...18), I do replace date column with my "row number" column. and want to fill empty rows with the last number of rownumber. 
this is my dax query for running total;

Count of ADMISSIONOID running total in RowNumber =
CALCULATE(
DISTINCTCOUNT('vw_SIS_STUDENT_ENROLLMENT'[ADMISSIONOID]);
FILTER(
ALLSELECTED('vw_SIS_STUDENT_ENROLLMENT'[RowNumber]);
ISONORAFTER('vw_SIS_STUDENT_ENROLLMENT'[RowNumber]; MAX('vw_SIS_STUDENT_ENROLLMENT'[RowNumber]); DESC)
)
)

[emremehmet]

Hi everyone, I need quick help, I know there is another message with same subject but I have another trick in my table.

first, I have increment numbers instead dates, (like 1-2-3-4-5-...18), I do replace date column with my "row number" column. and want to fill empty rows with the last number of rownumber. 
this is my dax query for running total;

Count of ADMISSIONOID running total in RowNumber = 
CALCULATE(
DISTINCTCOUNT('vw_SIS_STUDENT_ENROLLMENT'[ADMISSIONOID]);
FILTER(
ALLSELECTED('vw_SIS_STUDENT_ENROLLMENT'[RowNumber]);
ISONORAFTER('vw_SIS_STUDENT_ENROLLMENT'[RowNumber]; MAX('vw_SIS_STUDENT_ENROLLMENT'[RowNumber]); DESC)
)
)

[MrClod]

That appears to do the trick!!

Thank you!