cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Reverse Running Total with Blank Columns

My last post was reported for some reason. PLEASE DON'T REPORT AS SPAM! 

LINK TO WORKBOOK:  Google Drive Folder 

 

Got a weird one for you guys. I am building a reverse running total matrix that has the week in the quarter across the columns. The rows show the different year/quarter combinations. The measure is meant to reflect the revenue that was generated on or after the current week based on the column. So, for example, there was a total of $2,486,602 in revenue for 2019 Q1, as shown in the table below. In week 1, the entire revenue for the quarter is shown, because all revenue was generated on or after week 1 in the quarter. This amount should logically decrease for each week throughout the quarter. If no revenue was generated in a specific week, that column should reflect the same amount as the column before it...

 

That's where I'm running into an issue. When a given week does NOT have any revenue data, the matrix cell is NOT showing the same value as the prior column. Instead, it is showing the the full revenue for the quarter (equal to the value in the week 1 column).

 

See the yellow highlighted cells below for an example. For 2019 Q1, week 13 reflects the same value as week 1. I have no idea why the same didn't happen for 2019 Q4...

Got a weird one for you guys. I am building a reverse running total matrix that has the week in the quarter across the columns. The rows show the different year/quarter combinations. The measure is meant to reflect the revenue that was generated on or after the current week based on the column. So, for example, there was a total of $2,486,602 in revenue for 2019 Q1, as shown in the table below. In week 1, the entire revenue for the quarter is shown, because all revenue was generated on or after week 1 in the quarter. This amount should logically decrease for each week throughout the quarter. If no revenue was generated in a specific week, that column should reflect the same amount as the column before it...

 

That's where I'm running into an issue. When a given week does NOT have any revenue data, the matrix cell is NOT showing the same value as the prior column. Instead, it is showing the the full revenue for the quarter (equal to the value in the week 1 column).

 

See the yellow highlighted cells below for an example. For 2019 Q1, week 13 reflects the same value as week 1. I have no idea why the same didn't happen for 2019 Q4...

 

No Slicer.JPG

 

Here is another example, this time with 2019 Q1 split by region. You can see that the same thing is happening for any week where there is no revenue data for the week/year/quarter/region combination.

 

Region Slicer.JPG

 

I need those yellow cells to be equal to the prior column. Here's what I've already tried:

  • I've tried adding "+ 0" to the end of the measure. Doesn't work.
  • I've tested adding $0 lines to the data table this pulls from. That does work, however it is not an ideal solution for a host of reasons. I don't want dummy data in the model if I can help it.
 

I'm fairly certain this should be doable by adjusting the measure formula, but all of the forum posts I've seen regarding this topic discuss how to fix the blank column issues for running totals and not for reverse running totals.

 

Thanks so much for your help! Here is my measure formula for your reference:

 
Revenue On or After =
CALCULATE(
    SUM('Revenue Detail Table'[Revenue]),
    FILTER(
        ALLSELECTED('Revenue Detail Table'[Week]),
        'Revenue Detail Table'[Week] >= MIN('Revenue Detail Table'[Week])
    )
)
 
11 REPLIES 11
Highlighted
Regular Visitor

Reverse Running Total with Blank Columns

 

Got a weird one for you guys. I am building a reverse running total matrix that has the week in the quarter across the columns. The rows show the different year/quarter combinations. The measure is meant to reflect the revenue that was generated on or after the current week based on the column. So, for example, there was a total of $2,486,602 in revenue for 2019 Q1, as shown in the table below. In week 1, the entire revenue for the quarter is shown, because all revenue was generated on or after week 1 in the quarter. This amount should logically decrease for each week throughout the quarter. If no revenue was generated in a specific week, that column should reflect the same amount as the column before it...

 

That's where I'm running into an issue. When a given week does NOT have any revenue data, the matrix cell is NOT showing the same value as the prior column. Instead, it is showing the the full revenue for the quarter (equal to the value in the week 1 column).

 

See the yellow highlighted cells below for an example. For 2019 Q1, week 13 reflects the same value as week 1. I have no idea why the same didn't happen for 2019 Q4...

No SlicerNo Slicer

 

Here is another example, this time with 2019 Q1 split by region. You can see that the same thing is happening for any week where there is no revenue data for the week/year/quarter/region combination.

Region SlicerRegion Slicer

 

I need those yellow cells to be equal to the prior column. Here's what I've already tried:

  • I've tried adding "+ 0" to the end of the measure. Doesn't work.
  • I've tested adding $0 lines to the data table this pulls from. That does work, however it is not an ideal solution for a host of reasons. I don't want dummy data in the model if I can help it.
 

I'm fairly certain this should be doable by adjusting the measure formula, but all of the forum posts I've seen regarding this topic discuss how to fix the blank column issues for running totals and not for reverse running totals.

 

Thanks so much for your help! Here is my measure formula for your reference:

 
Revenue On or After =
CALCULATE(
    SUM('Revenue Detail Table'[Revenue]),
    FILTER(
        ALLSELECTED('Revenue Detail Table'[Week]),
        'Revenue Detail Table'[Week] >= MIN('Revenue Detail Table'[Week])
    )
)
Highlighted
Super User I
Super User I

Re: Reverse Running Total with Blank Columns

@drwismer 

 

Whats the create week column? and where does the column value (1 to 13) in the screenshot comes from?

Looking at the DAX, "MIN('Revenue Detail Table'[Create Week])" is returning 1 or 0 for the weeks you don't have an entry in the table.

 

Post some sample data with all the used columns to better assist you.

Connect on LinkedIn
Highlighted
Regular Visitor

Re: Reverse Running Total with Blank Columns

Oops. That should read "Week" not "Create Week." I've updated the post. All referenced columns are in the 'Revenue Detail Table' data table.

 

You are correct that the DAX appears to be returning 1 or 0 in the case of missing data. How can I get that to return the number at the head of the relevant column?

 

Thank you,

David

Highlighted
Super User I
Super User I

Re: Reverse Running Total with Blank Columns

@drwismer 

 

i did not.. I was responding to you..

Connect on LinkedIn
Highlighted
Regular Visitor

Re: Reverse Running Total with Blank Columns

I didn't mean to accuse you! I was just tagging you for visibility to this new post since the other one was deleted. Thanks for your responses.

Highlighted
Super User IV
Super User IV

Re: Reverse Running Total with Blank Columns

I love solving the weird ones. But I also hate typing. Can you post sample data please? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User I
Super User I

Re: Reverse Running Total with Blank Columns

@Greg_Deckler I am going to start using this from now on ...😀😀 You are just not only a legend in solving power bi problems🙏

 "I love solving the weird ones. But I also hate typing"

Highlighted
Regular Visitor

Re: Reverse Running Total with Blank Columns

 

@Greg_Deckler 

 

Thanks for the tip, Greg! I've scrubbed my Power BI Desktop file so it can be shared, but I'm not sure how to attach it to this post. Any ideas there? Is there some other way I should post sample data?

 

Thanks!

Highlighted
Super User IV
Super User IV

Re: Reverse Running Total with Blank Columns

Most folks just post it to OneDrive, Box, etc. and share a link.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors