Help Needed - Troubleshooting Blank Column Values

[PowerBi2bPro]

Hi Everyone,

I am using this Function:

Prv_Qtr_Assets = CALCULATE(sum ('Income Statement $'[Average Assets]), DATEADD('Income$MasterDate'[Date].[Date],-1,QUARTER))

My goal was to create a previous quarter value for Assets. 

Ther are values in Income Statement $'[Average Assets] Columns.

It contains values for each quarter (e.g. 31 Dec 2023, 30 Sep 2023 and so on)

I created MasterDate Table for sequential date.

I have created relationship between Master Date table and Income Statement Table

Using the Prv_Qtr_Assets = CALCULATE(sum ('Income Statement $'[Average Assets]), DATEADD('Income$MasterDate'[Date].[Date],-1,QUARTER)) - I am getting BLANK column instead of previous quarter value from the Average Assets column.

Thank you for helping

Regards

[v-jialongy-msft]

Hi @PowerBi2bPro 

Thank you for your continued help, @lbendlin .Is your problem solved now? @PowerBi2bPro 

Best Regards,

Jayleny

[PowerBi2bPro]

Thank you again. Would you recommend any courses that focuses on questions and things that I asked here? I did a few courses on LinkedIn, none seem to cover these type of specific scenarios. I also did youtube search, again could not find specific. there were lots of vidoes on Power BI DAX but nothing similar to what I am trying to do here. I am missing some fondation info. How do I learn that and where from? Thanks for your mentorship. 

[lbendlin]

I am rather biased and may not be the right person to give you advice - my preference has always been to get as close to the source as possible. 

Old but still the very best: https://www.amazon.com/Definitive-Guide-DAX-intelligence-Microsoft/dp/1509306978

Anything from SQLBI.com, basically.

[PowerBi2bPro]

Thank you. I will read more on the filter context. If we don't use the filter to create a measure, I just could not find how to do subtraction between a measure (e.g. Prv QTR)  and column (Current QTR). Can you please show an example how I might that work?

When I tried doing substraction without a measure for current QTR the new GAP measure didn't work. It said that you can't substract column and measure. 

[lbendlin]

that's right, for the current quarter you would need to bring your own aggregation, like SUM(column).  Like a measure does, but on the fly.

[PowerBi2bPro]

Thank you that was very helpful. I wanted to than create DELTA using these two. I tried to use Current Assets but that did not work.

I than created a new measure.  

Current QTR = CALCULATE(sum ('Income Statement $'[Average Assets]), DATEADD('Income$MasterDate'[Date],-0,QUARTER))

Than use GAP as another measure to calculate DELTA. 

I think this might work well. If I wanted to show Cumulative for each year only what Measure calculation would look like?

Thank you 

Gap = [Current QTR]-[Prv_Qtr_Assets]

[lbendlin]

DATEADD -0  ?

Read about REMOVEFILTERS.

[PowerBi2bPro]

Okay so the thing is that I want the current quarter as a measure so that I can create a new measure for the gap using the prev qtr measure that you recommended. 

What is the best way to achieve this?  -0 did give the current qtr value in the measure. Based on your question, it seems that there may be a better way to achieve this. If possible, please share your thoughts. 

[lbendlin]

Yes, please read about the concept of filter context in Power BI.  For the current quarter you can (but don't need to) create a measure in the current filter context, without a need to specify additional filters.

[PowerBi2bPro]

Thank you, I will try again. I am wondering how to attach a PIBX file the way you did. I probably am silly asking this. I don't see an attachment icon or option in the reply window. 

Thanksa gain for your help. I will get back to you as it sure not working on my end for my PIBX file. 

[lbendlin]

Your approach seems reasonable. The error may be elsewhere.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[PowerBi2bPro]

Okay so here is the Date Column

Year Sunday, December 31, 2023 Saturday, September 30, 2023 Friday, June 30, 2023 Friday, March 31, 2023 Saturday, December 31, 2022 Friday, September 30, 2022 Thursday, June 30, 2022 Thursday, March 31, 2022 Friday, December 31, 2021 Thursday, September 30, 2021 Wednesday, June 30, 2021 Wednesday, March 31, 2021 Thursday, December 31, 2020 Wednesday, September 30, 2020 Tuesday, June 30, 2020 Tuesday, March 31, 2020 Tuesday, December 31, 2019 Monday, September 30, 2019 Sunday, June 30, 2019 Sunday, March 31, 2019 Monday, December 31, 2018 Sunday, September 30, 2018 Saturday, June 30, 2018 Saturday, March 31, 2018 Sunday, December 31, 2017 Saturday, September 30, 2017 Friday, June 30, 2017 Friday, March 31, 2017 Saturday, December 31, 2016 Friday, September 30, 2016 Thursday, June 30, 2016 Thursday, March 31, 2016 Thursday, December 31, 2015 Wednesday, September 30, 2015 Tuesday, June 30, 2015 Tuesday, March 31, 2015 Wednesday, December 31, 2014 Tuesday, September 30, 2014 Monday, June 30, 2014 Monday, March 31, 2014 And the Assets Values are Total Assets 27408122 26509769 25332322 26271688 22286667 25040832 20040832 26939596 24435452 22357669 21180529 23543250 24505919 26548231 22130804 22136679 20949843 20785811 19597814 18325954 21515747 20020337 20148203 20624152 19088009 22220718 18857304 16690154 17796706 18882873 18850505 19996543 16654922 14467134 11169202 13336748 14405544 15503513 15066288 14033294   Hope this helps. I looked at how to upload the data link, and tried to follow the insert table option. Not sure if it is working. Let me know if you have any questions. Thanks again for your help.

[lbendlin]

Your formula seems to be fine (minus the unnecessary auto date/time - you forgot to mark the Dates table as such)

[PowerBi2bPro]

Thank you again for sharing the PIBX file. It was useful. I tried to open it and play with it to see what I get for Prv qtr and current qtr in the matrix view. Following is the outcome I got. 

Above is what I am getting when putting them in to matrix. This seems still odd as the same number is displayed for both dates. Prv and Current Qtr.

Following was the setting that I used for the above matrix. 

Please let me know that I am making some fundamental error, or why am I not getting different results. 

[lbendlin]

Please show the expected outcome based on the sample data you provided.

[PowerBi2bPro]

Thank you again. Here is a quick visual (done in PAINT) on what I am trying to get for an outcome. Not sure how to do it in Power BI.

Two Columns.

One for Current Year Asset Value and One for Previous Quarter Value

They should align in a way that at any given quarter I should be able to see both values side by side of what is current and waht was previous qtr.

My intention is to than substract current from previous to find the DELTA. 

When we plot the graph, it automatically adds SUM and double counts the already aggregated raw data values. as the current quarter values are aggrgated qtr over qtr.

Hope this helps adding some more context. Thank you for your time and help. 

[lbendlin]

One for Current Year Asset Value and One for Previous Quarter Value

Did you mean "Current Quarter"  rather than "Current Year" ?

My included PBIX has the "previous quarter"  measure - is that not sufficient?

[PowerBi2bPro]

Sorry for any confusion. If you see the attached visual, you will notice that 2014 for the current and previous year table shows the same value. The image is from the PIBX that you shared. I just switched it to table format. I am not able to show the previous and current with different values. They are showing up as same. 

What I want is the middle table but it should represnt valued for previous quarter in that column. 

Link to the file: https://drive.google.com/file/d/1wwZJ7fpfImknqBdfDb0Yh0zQPUUnz6Kx/view?usp=sharing

Thanks for the help 

[lbendlin]

You are taking the date from the wrong table.