Forum Discussion
comp date measure
Hi jherbert1017
I build two table like yours to have a test.
Transaction table:
You don’t need to merge Transaction table and Calendar Table. And if you feel your report is slow and performance is not good, you may need to optimize performance. For more info about optimize performance:Optimization guide for Power BI
You may achieve your goal by measure.
Firstly we use Visit Date in Calendar Table to build a Slicer.
Then we build a measure as below:
TotalIncome = CALCULATE(SUM('Transaction'[Income]),FILTER(ALL('Transaction'),'Transaction'[Visit Date]=MAX('Calendar'[Visit Date])))Add this measure in a card visual or Transaction table visual to show the result.
Result:
Select 2019/7/4 in slicer
You can download the pbix file from this link: Comp date measure
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
you have to create a Calendar_dimensional table.
| Calendar_Date | Period | Month_Name | Quarter | Quarter_Name | Week_of_Period | Week_of_Year | Week_Name | Year | Day_of_Period | Day_of_Year | Day_of_Week | Current_day_of_year |
| 12/30/2019 | 1 | January | 1 | Q1 | 1 | 1 | Week 1 | 2020 | 1 | 1 | 1 | 154 |
| 12/31/2019 | 1 | January | 1 | Q1 | 1 | 1 | Week 1 | 2020 | 2 | 2 | 2 | 154 |
then you connect the calendar date to your transaction date
create a measure similar to this
=calculate(sum(sales),filter(dim_calendar,dim_calendar[day_of_year] = dim_calendar[current_day_of_year] && dim_calendar[year] = dim_calendar[year] - 1))
if this doenst make sense can you send some sample data and then i can share back with you