Forum Discussion
Sales Date Comparative
Could you help me with a problem. I have a date table (which has a sales date, and a comparative date) and a sales table (where I have a sales date and a sales quantity). These tables are related by date. But I need to create a metric that brings me the total sales of the equivalent date field. I tried related but could not.
Excel Example
5 Replies
Join the Sales table twice with the date table.
Once with the date. Second time with compare date. Make date as active relation.
Now create formulas using calculate. In that use userelation to choose the join.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
Thanks.Do not join. In calculate remove join of date. By using cross join or cross filter
Sales = Var _start_date=(minx('Date',STARTOFYEAR('Date'[Date]))) Var _end_date=(max('Date'[Date])) Var _last_year_mtd_val= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && (Sales[Sales Date]) <= _end_date, crossjoin(.....) ) return _last_year_mtd_valThis not exactly the same. but a good example. Please also check the link to the second article in the article
https://medium.com/chandakamit/power-bi-comparing-data-across-date-ranges-36be49b68613
Create a relation between 'table sales'[Date] AND 'table dates'[date comparative] and mark it as inactive.
Use following measure
CALCULATE([your calculation], USERELATIONSHIP('table sales'[Date], 'table dates'[date comparative]))
