Wrong sum total table of measure column

[danirc]

Good afternoon,

I have a problem with a measure created in dax. I have an application that creates date calendars and each date you can assign a comparable date. Multiple dates can have the same comparable date. When this happens, the power BI tables only add the sale of one date repeated because it detects that it is the same foreign key.

I give you an example and the measure:

TotalNetComparableDate = CALCULATE(SUM(orders[net]),USERELATIONSHIP(orders[calendarFK],calendars_days[calendarComparableFK]))

Column comparableSale is TotalNetComparableDate measure

date	comparableDate	netSale	comparableSale
11/10/2020	13/10/2019	2.767,93 €	2.071,68 €
12/10/2020	13/10/2019	2.875,71 €	2.071,68 €
TOTAL		5.643,64 €	2.071,68 €

[wdx223_Daniel]

TotalNetComparableDate = CALCULATE(SUM(orders[net]),calendars_days[calendarFK] IN VALUES(calendars_days[calendarComparableFK]))

[danirc]

Comparable sell column appears empty.

[danirc]

Doesn't anyone have a valid solution?

[Anonymous]

@danirc Hey Mate ,

You can try this .hope this will work for you ,

TotalNetComparableDate =
CALCULATE(SUM(orders[net]),
CROSSFILTER(orders[calendarFK],
calendars_days[calendarComparableFK],
Both
)
)
You can give kudos as well if this work and accept this as a solution .

Thank you 

[Anonymous]

@danirc Hey Mate ,
try this then 

CALCULATE (

    SUM (orders[net]),

    TREATAS (

        VALUES ( ‘orders’[calendarFK]),

                     calendars_days[calendarComparableFK]    
             )

)

You can refer Below link for creating virtual relationship.

https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/ 

[danirc]

I didn't know about that function, thank you.

I have tried it but it returns empty column.

[danirc]

Thanks for the help but I get this. Net sales of actual year is obtained in the column of comparable sales.

[Anonymous]

@danirc 
can you share your previous dax formula which you use to calculate comparible sale .

how will you get the comparible date .

TotalNetComparableDate =
CALCULATE(SUM(orders[net]),
CROSSFILTER(orders[calendarFK],
calendars_days[calendarComparableFK],
Both
)
,values(calendars_days[calendarComparableFK]))
or
TotalNetComparableDate =
CALCULATE(SUM(orders[net]),
USERELATIONSHIP(orders[calendarFK],
calendars_days[calendarComparableFK]
)
,all(calendars_days[calendarComparableFK]))

or 

TotalNetComparableDate =
CALCULATE(SUM(orders[net]),
USERELATIONSHIP(orders[calendarFK],
calendars_days[calendarComparableFK]
)
,Values(calendars_days[calendarComparableFK]))

or 
can you share a sample pbix file 
that will great to understand the requirement 

You can give kudos as well if this work and accept this as a solution .

Thank you 

[danirc]

Thanks for your help, I have tried all the solutions but it returns the same result.
I'll explain the table structure to see if we can find the solution.
I have a stores table that is related to an orders table.
Each order is linked by the id of the store with the table of stores.
I also have a calendar table in which each store has its calendar and each day of the calendar has a comparable date in the same table.
Finally I have created a foreign key creating a new column joining the date with the store id in the orders table and the same in the calendar table.
In this way I have related the orders with their dates in the calendar, but to obtain the orders of the comparable dates I create the same relation but in the calendars table I create the foreign key with the comparable dates.

I can not pass you the pbx because it contains many more relationships, I give you images of this specific problem.
This image is from the calendar table, it relates to the orders table through calendar-> calendarFK to orders -> calendarFK and another inactive relation: calendar-> comparableCalendarFK to orders -> calendarFK.

Thank you very much.

[danirc]