Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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...
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.
I need those yellow cells to be equal to the prior column. Here's what I've already tried:
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:
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...
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.
I need those yellow cells to be equal to the prior column. Here's what I've already tried:
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:
@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.
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
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
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!
Most folks just post it to OneDrive, Box, etc. and share a link.
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?
Ah, I see. I've posted the file to Google Drive. Would you mind taking a look?
Thank you!
Link:
@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"
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |