Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |