Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
PhoTaiGuy
Frequent Visitor

Creating a Month Over Month Column with Blank Data Present

Hello everyone,

 

I'm a bit lost on the right approach to achieve this. I'm attempting to create a Table or Matrix with a column showing the change in value from a user selectable month to another user selectable month. The kicker is that if there were no sales of an item for a specific month then I have no data to represent that. 

 

Long time viewer but first time poster. My attempt at an example table to show what I'm trying to achieve... : 

 

ItemJune 2022Sep 2022Change
 Mugs 20 30 +10
 Plates 30  -30
 Bowls 10 +10

 

I found this code snippet which works if there are no blanks in the data. If there are blanks in the data then HASONVALUE comes up as True and it just takes the single month value instead of doing (0 -  value) or (value - 0).

 

How could I show a blank or gap in data as a 0? 

 

 

Month Change = 
    IF (
        hasonevalue ( 'Sheet1'[Period] ) , 
        SUMX (
            'Sheet1', 'Sheet1'[Sales]
        ),
        var mn = min ( 'Sheet1'[Period] )
        var mx = max ( 'Sheet1'[Period] )
        var mnsum = calculate ( SUM ('Sheet1'[Sales]) ,'Sheet1'[Period] = mn )
        var mxsum = calculate ( SUM ('Sheet1'[Sales]) ,'Sheet1'[Period] = mx )
           
        return 
            mxsum - mnsum
    )

 

 

Any help would be great! Thank you! 

1 ACCEPTED SOLUTION
PhoTaiGuy
Frequent Visitor

Got this to work by creating a new table and creating a 1 to many relationship using:

 

Table = DISTINCT( 'Sheet1'[Period])

 

And then my matrix and my Month over Month measure referenced this new 'Period' table/column. 

View solution in original post

7 REPLIES 7
PhoTaiGuy
Frequent Visitor

Got this to work by creating a new table and creating a 1 to many relationship using:

 

Table = DISTINCT( 'Sheet1'[Period])

 

And then my matrix and my Month over Month measure referenced this new 'Period' table/column. 

HughLa
Resolver IV
Resolver IV

Can you share your .pbx file with sample data here?

I didn't see an option to attach the files so I uploaded to a One Drive folder:

 

Removed Link

 

I have the slicer to show October and November sales. The Bowls in this case would be expected to be (0 - 10 = -10) instead of (blank - 10 = +10).

 

I think because November Bowl sales is blank, the measure can only look at October and then HASONEVALUE comes up as true so no subtraction takes place?

 

 

HughLa
Resolver IV
Resolver IV

Hi @PhoTaiGuy 

 

If I understand your question correctly, you want to show all of the months in your table? 

On your columns you can select Show items with no data. Or in your your measure you can add +0 to your calculation.

HughLa_0-1669832921501.png

 

Regards

Hugh

Thank you for reviewing Hughla. 

 

"Show items with no data" doesn't create a visible change.

 

I've tried adding the +0 to the measure, or even a random number, and it doesn't go through as a number that can be used later. If I put +25 to the measure it still shows up as 0 and my difference formula won't take that into account. 

 

Maybe the issue is that the raw data doesn't have anything for Plates in September 2022 (in my original example) so I can't force a value to that missing data point?

Hi @PhoTaiGuy 

 

Ok, I misunderstood your question.

 

Create a measure that returns the value of your column +0, so something like this:

Sales = SUM('Sheet1'[Sales]) +0

 

Then use that as your value in your table. That should return 0 if there is no values.

 

Let me know if that works.

Hugh

Thanks again @HughLa 

 

Injecting the +0 didn't work for me. Tried to take it a step further and played with adding +0 in various parts of the measure and that didn't work either. 

 

Playing around with the table and only using HASONEVALUE, the table won't even return a True or False. Just a blank.

 

ItemJune 2022Sep 2022Change
MugsTrueTrueFalse
PlatesTrueblankTrue
BowlsblankTrueTrue

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.