Current month vs prior year by product line

[Gman]

Hello

There are a ton of things out there showing how to compare to prior year but all of the ones I have seen show the date going down the left margin.

Date	Total Sales	Last Year Total Sales
1/1/20	200	150
1/2/20	300	100
1/3/20	1000	100

Unfortunately this will not work for my needs. I am trying to make a matrix in Power BI that will show each of my 17 product lines in the first column, current month sales in column 2, prior year same month in column 3, and variance for column 4. I thought I had it and was able to get a full matrix with all dates but when I filter to the current month there is no data in the prior year same month column. Below is my desired look.

Product	2020.Jan	2019.Jan	Variance
Product 1	100	50	50
Product 2	50	100	(50)

Also ideally when you opened the report it would show the most recent month.

Thank you for any help you can provide.

 

[Chihiro]

DAX is highly contextual formula language. I'd recommend that you upload sample pbix (zipped) file or if that's not possible, sample Excel file that has your tables (raw data with desensitized info).

Without it, it is bit difficult to help you.

 

[Gman]

Thank you for the suggestion, here is the sample excel file that has the raw data.

 

[Chihiro]

Ok, here's how I'd do it.

1. First add date dimension (calendar) table, from Jan 1 of earliest year to Dec 31 of last year. This can be done using PQ, DAX or some other means.
Example using PQ.

let
    sYear = Date.Year(List.Min(Table1[Date])),
    eYear = Date.Year(List.Max(Table1[Date])),
    Source = List.Dates(#date(sYear,1,1),Duration.Days(Duration.From(#date(eYear+1,1,1)-#date(sYear,1,1))),#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "MonthCode", each Text.Start(Date.MonthName([Date],"en-Us"),3)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Month", each Date.Month([Date])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "YearMonthLabel", each Text.From([Year])&"."&[MonthCode]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom3",{{"Date", type date}, {"Year", Int64.Type}, {"MonthCode", type text}, {"Month", Int64.Type}, {"YearMonthLabel", type text}})
in
    #"Changed Type"

2. Since date dimension table is used, Current Month column in the source table isn't needed. It is more efficient to calculate using DAX measure than using calculated column.

3. Load both tables to data model and create relationship between Calendar's [Date] and Table1's [Date]

4. Add following measures.
CurrentMonth:=CALCULATE(SUM([Sales]),FILTER('Calendar','Calendar'[Date]=MAX([Date])))
PrevYearMonth:=CALCULATE(SUM([Sales]),SAMEPERIODLASTYEAR('Calendar'[Date]))
Variance:=[CurrentMonth]-[PrevYearMonth]

5. Then in matrix, add PremierTranDescr as row. and measures into values.

6. You can add slicer to control month selection it will work for any month selected.

If you want to select last month automatically. You will need to use table as variable in DAX measure (which I wouldn't recommend in this case).
I like to keep measures dynamic to allow for more interactive experience.

See attached sample.

 

[Gman]

Worked like a charm thank you Chihiro !!

Is there anyway to make the chart titles dynamic to show the months data it's returning.

in this case it would be 2019.Dec and 2018.Dec.

Thank you again for all your help.

 

[Chihiro]

If you are fine with only 2019.Dec showing.

You can add [YearMonthLabel] to Columns. But I'd recommend adding 1 or 2 slicer controls with single selection forced.


Alternately, you can add cards that show [YearMonthLabel]

Ex: SelectedMonth = SELECTEDVALUE('Calendar'[YearMonthLabel])


As I mentioned, there are other ways to construct DAX measure to manipulate what calculations take place based on context. But that will render measure only useful as semi-static visual and does not allow selection based on month.

If you want the latest month selected automatically, you can add 'Calendar'[Date] into Filter field for the visual and use relative date filtering.

Ex:

 

[Gman]

This is perfect! thank you for all the help Chihiro!