Use relationship

Question

Hello,&nbsp;Need some help.&nbsp;I have my budgeted sales table which is linked to my calendar table. However , i want the match to be done on the month rather than date. i have inserted firstdate of the month for the Budgeted sales month so it is normal calendar will not find all the the dates, i want the matching to be done on the month ratherexample&nbsp;Budgeted Sales table containsDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Month&nbsp; &nbsp; &nbsp;Sales01/01/2025&nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;200001/02/2025&nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3000&nbsp;Now calendar containsDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; workingDayRank NumberOfDays&nbsp; month01/01/2025&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;24&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-- working day rank empty since non-working day02/01/2025&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;24&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- working day rank empty since non-working day03/01/2025&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 24&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 104/01/2025&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 24&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;I have to perform a calculation for each row (and i need to ignore the relationship on date)sales prorata = (sales *&nbsp;workingDayRank )/NumberOfDays&nbsp;&nbsp;&nbsp;Used below, but still it ignore January cause it seems it is still matching on the date.&nbsp;Budgeted Sakes Prorata (Rank) =CALCULATE(&nbsp; &nbsp;&nbsp;SUMX(&nbsp; &nbsp; &nbsp; &nbsp; BudgetSalesVol,&nbsp; &nbsp; &nbsp; &nbsp; DIVIDE&nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp;BudgetSalesVol[Volume] *&nbsp;IF(ISBLANK(RELATED('calendar'[WorkingDayRank_Month])),0,RELATED('calendar'[WorkingDayRank_Month])),&nbsp; &nbsp;&nbsp;RELATED('calendar'[NumberOfWorkingDaysInMonth])&nbsp; &nbsp; )&nbsp; &nbsp; ),REMOVEFILTERS('calendar'[Date]),&nbsp; &nbsp;&nbsp;REMOVEFILTERS(BudgetSalesVol[Date]),&nbsp; &nbsp;&nbsp;USERELATIONSHIP('calendar'[MonthNumber] ,BudgetSalesVol[Month]))&nbsp;Thanks

anonymous · Answer

Hi&nbsp;PowerDev1977&nbsp;
&nbsp;
Try this:
&nbsp;
Budgeted Sales Prorata (Rank) = 
CALCULATE(
    SUMX(
        BudgetSalesVol,
        DIVIDE(
            BudgetSalesVol[Sales] * IF(ISBLANK(RELATED('calendar'[workingDayRank])), 0, RELATED('calendar'[workingDayRank])),
            RELATED('calendar'[NumberOfDays])
        )
    ),
    REMOVEFILTERS('calendar'[Date]),
    REMOVEFILTERS(BudgetSalesVol[Date]),
    TREATAS(VALUES('calendar'[Month]), BudgetSalesVol[Month])
)
&nbsp;
In this formula:
&nbsp;
TREATAS is used to create a virtual relationship between the Month columns of the calendar and BudgetSalesVol tables.
REMOVEFILTERS is used to ignore the existing date relationships.
&nbsp;
Regards,
Nono Chen
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

powerdev1977 · Answer

Hi NOno,&nbsp;Thank you.&nbsp;However, im not sure we can use related here because i have a many to many relationship between calendar and BudgetSalesVol. So it doesnt find 'Calendar'[WorkingDayRank_month].&nbsp;I thought when we use UseRelationship it would ignore the many to many but it doesnt.Any clue pleaseThanks

Forum Discussion

Use relationship

2 Replies

Related content

Relationship

Relationships

Relationship Issue

Creating relationships

PowerBI Relationships