Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have a model that there isn't a date column in my fact table. So i didn't create a date table and i had to use month and year columns of the fact table (fModelData) to create the measures.
In order to create a previous year measure, i am using the measure below. However is not working. The results show BLANK.
I think can be something related to subtraction -1.
Notes:
Hours_Watched is a measure = Calculate (sum(Hours_Watched))
Year data type is whole number
Solved! Go to Solution.
Hi,
If you already have a Year and Month column, then write a calculated column formula to create a Date column
Date = 1*("1/"&fModelData[Month]&"/"&fModelData[Year])
Now create a relationship from this Date column to the Date column in the Calendar Table. In the Calendar Table, write calculated column formulas to extract Year, Month name and Month number. Sort the Month name by the Month number. To your visual(s), drag Year and Month name from the Calendar Table.
Previous Year_Hours Watched = calculate([Hours_watched],previousyear(Calendar[Date]))
Hope this helps.
Hi,
If you already have a Year and Month column, then write a calculated column formula to create a Date column
Date = 1*("1/"&fModelData[Month]&"/"&fModelData[Year])
Now create a relationship from this Date column to the Date column in the Calendar Table. In the Calendar Table, write calculated column formulas to extract Year, Month name and Month number. Sort the Month name by the Month number. To your visual(s), drag Year and Month name from the Calendar Table.
Previous Year_Hours Watched = calculate([Hours_watched],previousyear(Calendar[Date]))
Hope this helps.
This works perfectly as expected! Thanks!
You are welcome.
@rodrigosrm2 , If I have Year and month In my model. I would prefer to create a date like
Date = date([Year], [Month no],1) // or eomonth(date([Year], [Month no],1),0) \
then you can try
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
Or in you month year tbale have rank on YYYYMM , a new column
Month Rank = RANKX(all('Date'),'Date'[Month],,ASC,Dense)
This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
Last year Month= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=(max('Date'[Month Rank]) -12)))
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
56 | |
56 | |
38 | |
29 |
User | Count |
---|---|
75 | |
62 | |
45 | |
40 | |
40 |