• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Current month vs prior year by product line

Gman

Member
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.
DateTotal SalesLast Year Total Sales
1/1/20200150
1/2/20300100
1/3/201000100

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.
Product2020.Jan2019.JanVariance
Product 11005050
Product 250100(50)

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

Thank you for any help you can provide.
 

Chihiro

Excel Ninja
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.
 

Chihiro

Excel Ninja
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.
Code:
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.
 

Attachments

Gman

Member
Worked like a charm thank you Chihiro !!

Is there anyway to make the chart titles dynamic to show the months data it's returning.
65298
in this case it would be 2019.Dec and 2018.Dec.

Thank you again for all your help.
 

Chihiro

Excel Ninja
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.
65299

Alternately, you can add cards that show [YearMonthLabel]

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

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:
65301
 
Top