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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
irnm8dn
Post Prodigy
Post Prodigy

Difference between 2 columns, periods of time

I have a column in one table representing Monthly Customer Spend.  A relationship exists between this table and a DateKey Table.

 

All of this is represented in a "Matrix" style table, which neatly shows me customer spend in each period of time.  Month, Quarter, Year, etc.

 

I would like to understand the difference in ustomer spend, between the two (or more) periods of time.

 

I am looking to understand the difference in spend, between two periods of time to determine whether a customer is increasing, decreasing spend between the periods of time.

 

All help is appreciated.

1 ACCEPTED SOLUTION

More than happy to 🙂

 

In the Query Editory I clicked the "Custom Column" button on the "Add Column" tab.

 

I then created the following formula, which uses the exsting two columns [Month] and [Year] in a string

 

"1 " & [Month] & " " & Number.ToText([Year])

 

custom.png

 

Once I created the column I renamed and changed the type to Date.

 

Does that help? 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

15 REPLIES 15
Phil_Seamark
Employee
Employee

Hi @irnm8dn,

 

The good news is that you will be able to build your matrix with all the  measures you mention.

 

It will be much easier for us to provide help in the form of calculated measures etc, if you can provide some small samples of your data.  🙂

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Once again @Phil_Seamark to the resuce!  Thanks.

 

The link below will take you to a sample dataset.  Please keep in mind that this is joined with a datekey table.  (Not the time-based columns in the sample data.)

 

https://www.dropbox.com/s/vzop8t9zve84fht/Sample%20Data%203.21.17.csv?dl=0

 

Please advise on the recommended calculated measures.

 

The end goal is to show the difference between 2 ore more periods of time.  Month vs. Month, Qtr vs. Qtr, Year vs. Year.

 

Cheers!

Hi @irnm8dn,

 

I've built a small sample PBIX file which I've uploaded here

 

Demo file

 

The essesnce is it makes the most of the columns in the DATE table to peform the PREVIOUSMONTH, PREVIOUSQUARTER & PREVIOUSYEAR calculations, and make sure these are the columns you use on the axis of your visuals.

 

Spend Stuff.png

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark

 

Thanks!  Seems like the right solution, but I am getting an error with the file download.  Any opportunity to place it in Dropbox?

 

Capture.PNG

 

Appreciate the help.

Oops  

 

I get the error too.

 

Please try this link

 

https://wgtnpowerbi-my.sharepoint.com/personal/phil_wgtnpowerbi_onmicrosoft_com/_layouts/15/guestacc...

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hello team - COuld you share this PBIX file to me as well?

Thanks @Phil_Seamark

 

Last question.  What are you applying to have your dates lay in proper chronological order?

 

You're a life saver!

Hi @irnm8dn

 

Which dates in particular?  

 

If you  mean months, I make sure the column called [Month] in the Dates table is sorted by [MonthID] from the same table

 

 

Sort by Month.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark Thanks!

 

Phil, have a couple of questions about your .pbix file.

 

1.  When I try and follow your lead using your syntax (below), I receive this error, CALENDARAUTO function can not find a base column of DateTime type in the model.  Any suggestions, how I may create the "Dates" table as you did?

 

Dates = ADDCOLUMNS(
CALENDARAUTO() ,
"MonthID" , INT(FORMAT([Date],"YYYYMM")),
"Month" , FORMAT([Date],"MMM YYYY"),
"Year" , INT(FORMAT([Date],"YYYY")) ,
"QuarterID" , FORMAT([Date],"YYYYQ"),
"Quarter" , "Quarter " & FORMAT([Date],"Q") & " FY " & YEAR([Date])
)

 

2.  In the "Data" table you added a column called Date, which I believe is the connection between the two tables.  How did you do that?

 

As always, thanks.

 

 

And for point 2) it looks like I added a custom column in the Query Editor

 

    #"Added Custom" = Table.AddColumn(#"Removed Blank Rows", "Custom", each "1 " & [Month] & " " & Number.ToText([Year])),

This takes your [Month] and [Year] columns adds a string "1" to the front to make a text date eg. "1 January 2017" which I then parse to a date in the next statement.  This allows me to create a relationship to my Calendar table.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark

 

Can you offer the steps and syntax that you followed when adding the custom column?

More than happy to 🙂

 

In the Query Editory I clicked the "Custom Column" button on the "Add Column" tab.

 

I then created the following formula, which uses the exsting two columns [Month] and [Year] in a string

 

"1 " & [Month] & " " & Number.ToText([Year])

 

custom.png

 

Once I created the column I renamed and changed the type to Date.

 

Does that help? 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark

 

Relative to the calculations you created in the .pbix files, how would you write the syntax for spend current year?

 

This will allow me to do Year vs. Year?

 

Thanks...

Hi @irnm8dn

 

The CALENDARAUTO function scans all the other tables in your model to see if any have a column that is DATE or DATETIME.

 

This error suggests you don't have one.  You can always hardcode start/end dates using CALENDAR(DATES(2012,1,1) , DATES(2018,1,1) to give you an on the fly calendar.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks @Phil_Seamark

 

Two remaining questions:

 

In the dataset itself, you added a column called Date.  How did you do that?

 

When copying your "Dates" syntax:

 

Dates = ADDCOLUMNS(
CALENDARAUTO() ,
"MonthID" , INT(FORMAT([Date],"YYYYMM")),
"Month" , FORMAT([Date],"MMM YYYY"),
"Year" , INT(FORMAT([Date],"YYYY")) ,
"QuarterID" , FORMAT([Date],"YYYYQ"),
"Quarter" , "Quarter " & FORMAT([Date],"Q") & " FY " & YEAR([Date])
)

 

I receive a messag: CALENDARAUTO function can not find a base column of DateTime type in the model.

 

Any idea?

 

Thanks!

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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