topic Re: Help Needed - Troubleshooting Blank Column Values in DAX Commands and Tips

Help Needed - Troubleshooting Blank Column Values

PowerBi2bPro — Fri, 09 Feb 2024 23:41:18 GMT

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

Re: Help Needed - Troubleshooting Blank Column Values

lbendlin — Sat, 10 Feb 2024 21:47:11 GMT

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-Forum/ba-p/963216

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/1447523

Re: Help Needed - Troubleshooting Blank Column Values

PowerBi2bPro — Wed, 14 Feb 2024 20:20:08 GMT

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.

Re: Help Needed - Troubleshooting Blank Column Values

lbendlin — Wed, 14 Feb 2024 22:11:36 GMT

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

Re: Help Needed - Troubleshooting Blank Column Values

PowerBi2bPro — Thu, 15 Feb 2024 14:43:32 GMT

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.

Re: Help Needed - Troubleshooting Blank Column Values

PowerBi2bPro — Thu, 15 Feb 2024 15:26:29 GMT

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.

Re: Help Needed - Troubleshooting Blank Column Values

lbendlin — Thu, 15 Feb 2024 15:34:10 GMT

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

Re: Help Needed - Troubleshooting Blank Column Values

PowerBi2bPro — Thu, 15 Feb 2024 16:51:07 GMT

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.

Re: Help Needed - Troubleshooting Blank Column Values

lbendlin — Thu, 15 Feb 2024 22:40:26 GMT

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?

Re: Help Needed - Troubleshooting Blank Column Values

PowerBi2bPro — Thu, 15 Feb 2024 22:58:40 GMT

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

Re: Help Needed - Troubleshooting Blank Column Values

lbendlin — Thu, 15 Feb 2024 23:04:12 GMT

You are taking the date from the wrong table.

Re: Help Needed - Troubleshooting Blank Column Values

PowerBi2bPro — Thu, 15 Feb 2024 23:27:00 GMT

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]

Re: Help Needed - Troubleshooting Blank Column Values

lbendlin — Fri, 16 Feb 2024 00:31:36 GMT

DATEADD -0 ?

Read about REMOVEFILTERS.

Re: Help Needed - Troubleshooting Blank Column Values

PowerBi2bPro — Fri, 16 Feb 2024 14:33:02 GMT

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.

Re: Help Needed - Troubleshooting Blank Column Values

lbendlin — Fri, 16 Feb 2024 14:45:19 GMT

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.

Re: Help Needed - Troubleshooting Blank Column Values

PowerBi2bPro — Fri, 16 Feb 2024 14:50:26 GMT

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.

Re: Help Needed - Troubleshooting Blank Column Values

lbendlin — Fri, 16 Feb 2024 15:00:00 GMT

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.

Re: Help Needed - Troubleshooting Blank Column Values

PowerBi2bPro — Fri, 16 Feb 2024 15:04:49 GMT

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.

Re: Help Needed - Troubleshooting Blank Column Values

lbendlin — Fri, 16 Feb 2024 15:11:12 GMT

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.

Re: Help Needed - Troubleshooting Blank Column Values

Anonymous — Fri, 15 Mar 2024 08:00:20 GMT

Hi PowerBi2bPro

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

Best Regards,

Jayleny