cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 5
Super User IV

## Re: Sales 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.

Member

## Re: Sales Date Comparative

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]))`
Frequent Visitor

## Re: Sales Date Comparative

I tried to use relationship but my relationship with date 2 with sales is many to many. And it seems that does not work.

Super User IV

## Re: Sales Date Comparative

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

Highlighted
Community Support Team

## Re: Sales Date Comparative

HI, @Souza

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements