Prior year Matrix column

[RobbLewz]

Hi,  I have a matrix table in my report that I want to show total sales by employee, per month and have a total sales for the previous year.

I have a SQL

dimension called DimEmployee, (EmpId(PK), EmpName),

UK format...

dimenstion called DimDate (DateKey(PK), FullDate(DD/MM/YYY/), FiscalYear, FiscalMonth, FiscalMonthName) -- Fiscal Month is an int, (1 = April etc... used to order FidcalMonthName)  -- FiscalMonthName is a varchar, April, may, June, etc...

Fact table called FactSales(EmpId(FK), SaleAmount deciaml, DateKey(FK))

So I have a slicer that oulls its value from DimDate(FiscalYear) to filter the matrix to one fiscal year,

The Matrix visual is configured as

Rows - DimEmployee.EmpName

Columns - DimDate.FiscalMonthName

Values - FactSales.SaleAmount -(Summed from the drop down)

The table looks something like...

Employee	April	May	.... etc	Total (Calculated by the matrix)
Emp1	10	20	30	60
Emp2	5	10	10	25

However, we want to have a Prior Year total column at the start, so someting like....

Employee	Prior Year Total	April	May	.... etc	Total(YDT)
Emp 1	500	20	20	10	50

I can't find out, how to achive this though.

I have a Metrix table with 2 measures

Current year = SUM(FactSales[SalesAmount]) - filtered by the report slicer of fiscal year

Prior Year = CALCUALTE([CurrentYear], SAMEPERIODLASTYEAR(DimDate[FullDate]))

The values work fine in a bar chart, but I can find a way to implement it into the matrix in the format above

Anyone faced this issue?

[RobbLewz]

updated the original post to hopefully make more sense

[v-juanli-msft]

Hi @RobbLewz 

Matrix doesn't support this kind of format by default.

Would you minding adding rows to your data model or creating calculated tables?

This workaround  is not dynamic.

Best Regards
Maggie

[amitchandak]

Not clear,

In case you have a date table and need YTD or prior YTD, you can use datesytd and totalytd . In both these, you can give year end date

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(dateadd('Date'[Date],-1,Year),-21,Day))

"12/31" is year-end date, you can change it say "3/31", year end in march

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Greg_Deckler]

First, sample data and measure formulas are of great assistance. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

Also, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...