comp date measure

[jherbert1017]

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

[Anonymous]

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

[amitchandak]

@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

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

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-Y...


Appreciate your Kudos.

[Anonymous]

@jherbert1017 

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

[Anonymous · Accepted solution]

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.