Model without Date Column

[rodrigosrm2]

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.

Previous Year_Hours Watched =

Var P.Year = SELECTEDVALUE(fModelData[Year])-1

RETURN

CALCULATE(

[Hours_Watched],

FILTER(

fModelData,

fModelData[Year]=P.Year))

Notes:

Hours_Watched is a measure = Calculate (sum(Hours_Watched))

Year data type is whole number

[Ashish_Mathur · Accepted 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.

[rodrigosrm2]

This works perfectly as expected! Thanks!

[Ashish_Mathur]

You are welcome.

[amitchandak]

@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