Daily running total is incorrect for days with zero sales

[cho]

Hi Experts!

I have three tables in PowerBI desktop.

Contract: Business, Site_ID, Contract_ID

Date: DateID, Date

Sales: DateID, Contract_ID, Amount

Sales account is connected to other two tables based on DateID and Contract_ID. I need to get a Month-to_date summary on Business level and Site_ID level.

But the logic is, MTD values on Site_ID level, final daily MTD value should be positive. So all negative values should show as positive.

Then in Business level, all Site_ID level values should be added. That is all positive values should be added.

I added a measure and it works fine.

Measure=SUMX(summarize(Sales, Contract[Business], Contract[Site ID]) , calculate(abs( TOTALMTD(SUM(Sales[Amount]),Date[DATE]))))

But, I do not get the correct running total. That is, for the days without any sales, 'measure' shows as blank. Therefore, the total is incorrect. As an example, it shows like this.

Date	Sales	Measure	Correct measure should be
01/01/2023	-563	563	563
02/01/2023	-290	853	853
03/01/2023	683	170	170
08/01/2023			170
09/01/2023	583	413	413
10/01/2023	52	465	465
13/01/2023	-532	67	67
04/02/2023	65	65	65
05/02/2023			65
06/02/2023	124	189	189
27/02/2023	90	279	279

Can someone please help me to fix this formula? 

[cho · Accepted solution]

I used below meaure and it works. But now, daily totals are incorrect. @Greg_Deckler @Padycosmos 

Measure1=SUMX(summarize(Sales, Contract[Business], Contract[Site ID]) , calculate(abs( TOTALMTD(SUM(Sales[Amount]),Date[DATE]))))

Measure2 = 
VAR curr_mtd = [Measure1]
VAR prev_day_mtd = CALCULATE(
    [Measure1],
    FILTER(
        ALL(Date),
        Date[DATE] = MAX(Date[DATE]) - 1
            && DAY(MAX(Date[DATE])) <> 1
    )
)
VAR output =
    IF(
        ISBLANK(curr_mtd) || curr_mtd = 0,
        IF(
            ISBLANK(prev_day_mtd) || prev_day_mtd = 0,
            CALCULATE(
                [Measure1],
                FILTER(
                    ALL(Date),
                    Date[DATE] < MAX(Date[DATE])
                        && DAY(MAX(Date[DATE]) - 1) <> 1
                )
            ),
            prev_day_mtd
        ),
        curr_mtd
    )
RETURN output

Hidden are Site IDs.

[cho]

I used below meaure and it works. But now, daily totals are incorrect. @Greg_Deckler @Padycosmos 

Measure1=SUMX(summarize(Sales, Contract[Business], Contract[Site ID]) , calculate(abs( TOTALMTD(SUM(Sales[Amount]),Date[DATE]))))

Measure2 = 
VAR curr_mtd = [Measure1]
VAR prev_day_mtd = CALCULATE(
    [Measure1],
    FILTER(
        ALL(Date),
        Date[DATE] = MAX(Date[DATE]) - 1
            && DAY(MAX(Date[DATE])) <> 1
    )
)
VAR output =
    IF(
        ISBLANK(curr_mtd) || curr_mtd = 0,
        IF(
            ISBLANK(prev_day_mtd) || prev_day_mtd = 0,
            CALCULATE(
                [Measure1],
                FILTER(
                    ALL(Date),
                    Date[DATE] < MAX(Date[DATE])
                        && DAY(MAX(Date[DATE]) - 1) <> 1
                )
            ),
            prev_day_mtd
        ),
        curr_mtd
    )
RETURN output

Hidden are Site IDs.

[cho · Accepted solution]

I used below meaure and it works. But now, daily totals are incorrect. @Greg_Deckler @Padycosmos 

Measure1=SUMX(summarize(Sales, Contract[Business], Contract[Site ID]) , calculate(abs( TOTALMTD(SUM(Sales[Amount]),Date[DATE]))))

Measure2 = 
VAR curr_mtd = [Measure1]
VAR prev_day_mtd = CALCULATE(
    [Measure1],
    FILTER(
        ALL(Date),
        Date[DATE] = MAX(Date[DATE]) - 1
            && DAY(MAX(Date[DATE])) <> 1
    )
)
VAR output =
    IF(
        ISBLANK(curr_mtd) || curr_mtd = 0,
        IF(
            ISBLANK(prev_day_mtd) || prev_day_mtd = 0,
            CALCULATE(
                [Measure1],
                FILTER(
                    ALL(Date),
                    Date[DATE] < MAX(Date[DATE])
                        && DAY(MAX(Date[DATE]) - 1) <> 1
                )
            ),
            prev_day_mtd
        ),
        curr_mtd
    )
RETURN output

Hidden are Site IDs.

[Greg_Deckler · Accepted solution]

@cho First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[Padycosmos]

Hope both these two videos help:

https://www.youtube.com/watch?v=y7ucCjtADsg

https://www.youtube.com/watch?v=qJps43vWsWM

[Greg_Deckler]

@cho Try: Better Running Total - Microsoft Power BI Community

[cho]

Thank you @Greg_Deckler . Love this video and I already watched it. Will this work for month-to-date as well? As I need MTD, not just running total.

[Greg_Deckler]

@cho Sure, It's similar to Year to Date Total: https://youtu.be/LL_CDYFtgz8

A simple version of a Month to Date Total is:

MTD No Calculate = 
  VAR __Date = MAX('Dates'[Date])
  VAR __MinDate = DATE(YEAR(__Date),MONTH(__Date),1)
  VAR __Table = FILTER(ALLSELECTED('Table'),[Date] >= __MinDate && [Date] <= __Date)
  VAR __Result = SUMX(__Table, [Column])
RETURN
  __Result

[cho]

Thank you for the explanation @Greg_Deckler . Unforetunately, I still do not get the required results 😞 
Somehow, fixing below measure does now work and I can't think of any other alternative formula to get to the logic I need. Can you please help me more?

Measure=SUMX(summarize(Sales, Contract[Business], Contract[Site ID]) , calculate(abs( TOTALMTD(SUM(Sales[Amount]),Date[DATE]))))

Basically, what I need is to get is, if total sales for a particular day is 0 or null, get previous day's  cumulative running total as long as it is not the 1st of the month.