Forum Discussion
Helper Vprevious year calculation with time filter
Hi RMV
When you created your date table did you create the relationships?
I created the following measures based on your dataset.
My Amount = SUM(Table1[Amount]) YTD = TOTALYTD([My Amount],'Date'[Calendar Date]) Previous Yr My Amount = CALCULATE([My Amount],PREVIOUSYEAR('Date'[Calendar Date]))Which then gave me the following correct output.
Helper VHi GilbertQ,
thanks for your advise.
I need further advise on using the date table.
I apologize that my example did not really represent the condition.
Beside the Date & Amount column, there're some category columns that will be used to drill down the data from category to category.
The table result I'm looking for is as follow
Category 1 Amount YTD Amount YTD Previous Year
A
A-1 xxx xxx
A-2 xxx xxx
B
B-1 xxx xxx
Note: A & B are category 1
A-1, A-2, B-1 are category 2
If I use the date table, how can I still drill down to categories?
regards,
- RMV
Hi GilbertQ,
I tried it, and still got blank as the result.
Need further advise on what goes wrong.
I created a date table, add a measure column = CALCULATE(SUM(Table[Amount]),PREVIOUSYEAR(DateCalendar[Date]))
In the table matrix visualization:
Category Amount YTD Amount Previous Year
(column from Table) (measure from Table) (measure from DateCalendar)
Tried CALCULATE(SUM(Table[Amount]),SAMEPERIODLASTYEAR(DateCalendar[Date]))
It results blank too.
- GilbertQ
Super User
Hi RMV
When you created your date table did you create the relationships?
I created the following measures based on your dataset.
My Amount = SUM(Table1[Amount]) YTD = TOTALYTD([My Amount],'Date'[Calendar Date]) Previous Yr My Amount = CALCULATE([My Amount],PREVIOUSYEAR('Date'[Calendar Date]))Which then gave me the following correct output.