Get same month, previous year data in new column

[paris]

Hi All,

I want to get same month, previous year data in new column by using dax. 

1 Jan 2020 Previous column shows 1 Jan 2019 actual number. 

Please help!

All the best,

Paris

[Ashish_Mathur]

Hi,

Create a Calendar Table and add columns there for Year, Month name and Month number.  Sort the Month Name column by the Month number.  Create a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  To your visual, drag the Year and Month Name columns from the Calendar Table.  Creae these measures:

Actuals = SUM(Data[Atual])

Actuals in same period last year = Calculate([Actuals],sameperiodlastyear(Calendar[Date]))

Hope this helps. 

[v-eachen-msft]

Hi @paris ,

You could create a YM column at first.

YM = 
YEAR('Table'[Date])*100+MONTH('Table'[Date])

Then use EARLIER() function to get results.

Previous =
CALCULATE (
    FIRSTNONBLANK ( 'Table'[Actual], 1 ),
    FILTER ( 'Table', 'Table'[YM] = EARLIER ( 'Table'[YM] ) - 100 )
)

 Here is my test file for your reference.

[amitchandak · Accepted solution]

@paris , you can use time intelligence and date table. example

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

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[paris]

Hi Amitchandak,

Thank you for sharing 3 exmaples , it helps to understand what DAX means.

All the best,

Paris