Re: Reverse Running Total with Blank Columns

Anonymous · ‎02-27-2020

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

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])

)

Anonymous · ‎02-27-2020

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

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

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])

)

VasTg · ‎02-27-2020

@Anonymous

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

Anonymous · ‎02-27-2020

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

Greg_Deckler · ‎02-27-2020

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Anonymous · ‎02-27-2020

@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!

Greg_Deckler · ‎02-27-2020

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Anonymous · ‎03-02-2020

Tried working with EARLIER, but that seems to be more applicable to creating new columns within data tables rather than for building measures outside of the data tables. Any other ideas? Or any chance you could show me how to use EARLIER in the example data I linked to in my post?