Forum Discussion
comp date measure
New to Dax and trying to figure this out, I have two tables as below. Trying to calculate a measure that will sum the income based on the comp date when the slicer on the page is set to the visit date. There is a relationship between the two tables with the visit date columns in each, but i also merged the two tabels in the power query to add the comp date column to the transaction table. Wasn't sure which way was better on performance as I have roughly 70M rows of transactions(that will continue to grow) and am trying to optimize performance as well.
So ultimately the result i am looking for is when the visit date is 7/1/2020 would be income $15 (sum of 7/3/2019), visit date 7/2/2020 would be income $13 (sum of 7/4/2019) and visit date 7/3/2020 would be income $27 (sum of 7/5/2019)
Your help is greatly appreciated!
Transaction table
| Visit Date | Income | Comp Date |
| 7/3/2019 | 10 | 7/5/2018 |
| 7/3/2019 | 5 | 7/5/2018 |
| 7/4/2019 | 13 | 7/6/2018 |
| 7/5/2019 | 20 | 7/7/2018 |
| 7/5/2019 | 3 | 7/7/2018 |
| 7/5/2019 | 4 | 7/7/2018 |
| 7/1/2020 | 5 | 7/3/2019 |
| 7/1/2020 | 10 | 7/3/2019 |
| 7/2/2020 | 3.5 | 7/4/2019 |
| 7/3/2020 | 7 | 7/5/2019 |
| 7/3/2020 | 8 | 7/5/2019 |
Calendar Table
| Visit Date | Comp Date |
| 7/1/2020 | 7/3/2019 |
| 7/2/2020 | 7/4/2019 |
| 7/3/2020 | 7/5/2019 |
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.
4 Replies
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
jherbert1017 , Have a date table, join it with visit date active, and comp date inactive.
refer this blog How to work with inactive join using userelation
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Hi jherbert1017
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your onedrive business.
Best Regards,
Rico Zhou
