How to get last month and last year values

[Anonymous]

Hi All,

I have a Dataset (I'm attaching the PBI file herewith) which has several types of data for each month.

This Bar chart has drill-down option enable and I want the matrix table below that bar chart to have the last month values as per the selection

For example, if the user selects "Nov" from Month Code and since the chart is showing Nove values for both "Email" and "Phone" I need the below matrix table to show the last month's values for Email and Phone. Like that when a user drill the chart bar chart will show teams such as "Finance" and "HR". In this case, my matrix should show the last month's values for these teams

Sorry if my explanation is not clear. I'm anyway attaching a sample of my pbix file with this. 

https://drive.google.com/file/d/1lgm8kuDmLAPebFL10SipaH4VDcsHftnr/view?usp=sharing

[v-robertq-msft]

Hello, @Kasun93Rox

According to your image and description, you want to add an Matrix below the column chart to display last month's data, you can try my steps:

1. To make the Slicer selection filter the two charts with a different value, I think you should first remove the relationship from the two tables, as follows:

2. Create two measures:

This month Cout =

var _selectedmonthname=MAX('Date'[Month])

return

CALCULATE(SUM('Table'[Cout]),FILTER('Table',[Report Month]=_selectedmonthname))

Last month Cout =

var _selectedmonth=MAX('Date'[Month No])

var _lastmonthname=CALCULATE(MAX('Date'[Month]),FILTER(ALL('Date'),[Month No]=_selectedmonth-1))

return

CALCULATE(SUM('Table'[Cout]),FILTER('Table',[Report Month]=_lastmonthname))

3. Create an Array and place columns and measure like this:

4. Change the [Cout] column in the column chart to the created measure, such as this:

And you can get whatever you want, like this:

Before delving into:

After deepening:

You can download my test pbix file here

Best regards

Qin Community Support _Robert Team

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@Anonymous , with help from date table and time intelligence

Try like examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Year

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year 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"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

[Anonymous]

Hi @amitchandak,

Thank you for your reply. I'm unable to use date add function as it;s giving me an error of " dateadd expects a contagious selection when the date column..."

Is this because I'm using a separate filter? That filter is not using a table which i created manually. not a proper date table. That is due to some other requirement of mine.

[AllisonKennedy]

@Anonymous What is the other requirement? You should really use a proper date table. Time intelligence functions don't work properly without them. You can sometimes cheat by adding .[Date] to the date column in your custom non-datetable 

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

[Anonymous]

Hi Alison,

I had bad experiences while creating relationships with the date tables. Therefore I avoided it for this dashboard. But I know that there are a number of benefits of having it. Let's just say that I'm really bad at creating relationhips with each table.

Is there a way to create a date table only with the start or end date of the month without adding all the dates? I really don't need all the dates. Then if I derive the month code ("mmm" format) using a function and if my data tables has that month code as text can I make relatonships?

[StefanoGrimaldi]

you can use the union function for creating a new table to summarize the dates existing in each of your table (if you have multiple data tables) like that you will have only a date table of dates existing in your data, if you have only 1 data table you can simple use the summarize funtion for the date column and therefore create a date table of only those dates.

[AllisonKennedy]

@Anonymous  The date table must have every date for the entire year, not just start of month. However, you can convert "MMM" into a date if we know what year it's for, and then connect this to the date table by DateKey in format of yyyyMMdd. Even though you won't use every date, it still needs to be there so that time intelligence works. The trick is that even though your data is already aggregated at the month level, you still need to pick a date (Start of month is nice and easy as every month has 1st) to use as your key to relate to the date table. That's the part that confuses most people. If your data is in "MMM" format, you can use custom M code, or you can create a new custom column for Day and type ="01" in the formula bar box. Create another column for Year, though I'm not sure yet how you'll determine the year? Then you can merge the columns in the right order to get a date key. You can even have your date key use yyyyMMMdd format if you find that easier. 

Let me know what the raw data looks like as a preview if you want more specific advice/help on the date table connections.