Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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]))
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.
I tried to use relationship but my relationship with date 2 with sales is many to many. And it seems that does not work.
HI, @Anonymous
You may try this formula to create as below:
Measure=CALCULATE( SUM([SALES]), FILTER(ALL('Date'), [SALES DATE]=MAX([DATE])))
If not your case, please share some sample data and expected output.
Regards,
Lin
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_val
This 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |