## Desktop

Member
Posts: 310
Registered: ‎01-31-2017

# 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.

Accepted Solutions
Super User
Posts: 2,540
Registered: ‎11-29-2015

## Re: Difference between 2 columns, periods of time

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])`

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

Does that help?

Proud to be a Datanaut!

All Replies
Highlighted
Super User
Posts: 2,540
Registered: ‎11-29-2015

## Re: Difference between 2 columns, periods of time

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.

Proud to be a Datanaut!

Member
Posts: 310
Registered: ‎01-31-2017

## Re: Difference between 2 columns, periods of time

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

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!

Super User
Posts: 2,540
Registered: ‎11-29-2015

## Re: Difference between 2 columns, periods of time

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.

Proud to be a Datanaut!

Member
Posts: 310
Registered: ‎01-31-2017

## Re: Difference between 2 columns, periods of time

@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?

Appreciate the help.

Super User
Posts: 2,540
Registered: ‎11-29-2015

## Re: Difference between 2 columns, periods of time

Oops

I get the error too.

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

Proud to be a Datanaut!

Member
Posts: 310
Registered: ‎01-31-2017

## Re: Difference between 2 columns, periods of time

Thanks @Phil_Seamark

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

You're a life saver!

Super User
Posts: 2,540
Registered: ‎11-29-2015

## Re: Difference between 2 columns, periods of time

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

Proud to be a Datanaut!

Member
Posts: 310
Registered: ‎01-31-2017

## Re: Difference between 2 columns, periods of time

[ Edited ]

Thanks @Phil_Seamark

Two remaining questions:

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

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!

Member
Posts: 310
Registered: ‎01-31-2017

## Re: Difference between 2 columns, periods of time

[ Edited ]

@Phil_Seamark Thanks!

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?

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.