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.
Hi All,
I have a fact table with only Year & month.
I have created a Date Table using these two columns via Calendar function using Min Year & month and Max Year & Month and marked it as a Date Table.
But When I try to create a measure for Previous Period Calculations the results appears without any filters for the whole available data.
Measure:
SalesValue =
VAR SumValue=SUM(Daily_Sales[SalesValue])
VAR Result= CALCULATE(SumValue,SAMEPERIODLASTYEAR(d_Date[Date]))
Return Result
Any help is appreciated.
Solved! Go to Solution.
@mbuhary , You can then create date from year and month
example
date = date([year], [month],1) // I can suggest more ways
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]))
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"))
@mbuhary , You can then create date from year and month
example
date = date([year], [month],1) // I can suggest more ways
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]))
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"))
Thank you, I was thinking on the same lines, and it solved the Issue.
I removed the additional Table d_Year/Month and now directly created relationship of the fact table with date table. So now all Time Intelligence functions are working.
e.g. for same period last year,
Thank you for your reply in detail.
So do you mean that I will not be able to use any Time Intellignce functions using the Date Table ?
@mbuhary , With help of d_yearmonth you can get data of the previous year
new column in d_yearmonth
Month Rank = RANKX(all('d_yearmonth '),'Date'[YrMonth],,ASC,Dense)
example measure
This Month = CALCULATE(sum('daily_sales'[Qty]), FILTER(ALL('d_yearmonth '),'d_yearmonth '[Month Rank]=max('d_yearmonth '[Month Rank]))
Last Month = CALCULATE(sum('daily_sales'[Qty]), FILTER(ALL('d_yearmonth '),'d_yearmonth '[Month Rank]=max('d_yearmonth '[Month Rank])-1))
Last year Month = CALCULATE(sum('daily_sales'[Qty]), FILTER(ALL('d_yearmonth '),'d_yearmonth '[Month Rank]=max('d_yearmonth '[Month Rank])-12))
//Only year vs Year, not a level below
This Year = CALCULATE(sum('daily_sales'[Qty]),filter(ALL('d_yearmonth '),'Date'[Year]=max('d_yearmonth '[Year])))
Last Year = CALCULATE(sum('daily_sales'[Qty]),filter(ALL('d_yearmonth '),'Date'[Year]=max('d_yearmonth '[Year])-1))
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 |
---|---|
117 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
104 | |
102 | |
89 | |
66 |