Power BI Running Total return the same value

Question

I maintained 2 tables, Date-which only consist of date and Ticket. These tables are link by using CompletedDate from Ticket to Date_1 from Date. I wrote a DAX measure that will calculate the running total of the ticket based on CompletedDate. However for the dates that has no tickets being sold, it shown as blank. I want the blank to be filled with the last non-blank value. I had try the solution provided here https://community.powerbi.com/t5/Desktop/Filling-the-blanks-of-a-running-total-with-last-non-blank-value/td-p/169041, but no changes to the table. TicketClosed_DateTbl should've get me to the expected result, however the value remain the same.

TicketClosed_Total = 
CALCULATE(
    [TotalTicket_Pending],     
    FILTER(
        ALL('Ticket'[CompletedDate]),
        'Ticket'[CompletedDate] <= MAX('Ticket'[CompletedDate])
    )
)

TicketClosed_DateTbl = 
CALCULATE(
    [TicketClosed_Total],
    USERELATIONSHIP('Date'[Date_1],'Ticket'[CompletedDate])
)

where is 'Ticket'[CompletedDate] column in your sample data? also please add sample data in tabular format rather than Image. — mkRabbani

mkRabbani mkRabbani · Accepted Answer · 2020-09-02T10:30:50

You basically need 2 measure for counting cumulative total and sold ticket as below. I just created 1st date of each month as CompletedDate for calculation purpose.

For cumulative total ticket

cum_total_ticket = 
CALCULATE(
    SUM(Ticket[total ticket]),
    FILTER(
        ALL(Ticket),
        Ticket[CompletedDate] <= MIN(Ticket[CompletedDate])
    )
)

For cumulative total sold

cum_sold_ticket = 
CALCULATE(
    IF(
        SUM(Ticket[sold ticket]) = BLANK(),
        0,
        SUM(Ticket[sold ticket])
    ),
    FILTER(
        ALL(Ticket),
        Ticket[CompletedDate] <= MIN(Ticket[CompletedDate])
    )
)

The final output will be-

Power BI Running Total return the same value

1 Answers