Use relationship

[PowerDev1977]

Hello,

Need some help.

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 rather

example 

Budgeted Sales table contains

Date              Month     Sales

01/01/2025    1             2000
01/02/2025    2             3000

Now calendar contains

Date                    workingDayRank NumberOfDays  month

01/01/2025                                       24                        1           -- working day rank empty since non-working day

02/01/2025                                       24                        1          -- working day rank empty since non-working day

03/01/2025                 1                    24                        1

04/01/2025                 2                    24                         1

I have to perform a calculation for each row (and i need to ignore the relationship on date)

sales prorata = (sales * workingDayRank )/NumberOfDays  

Used below, but still it ignore January cause it seems it is still matching on the date. 

Budgeted Sakes Prorata (Rank) =

CALCULATE

(

    SUMX(

        BudgetSalesVol,

        DIVIDE

    (

       BudgetSalesVol[Volume] * IF(ISBLANK(RELATED('calendar'[WorkingDayRank_Month])),0,RELATED('calendar'[WorkingDayRank_Month])),

    RELATED('calendar'[NumberOfWorkingDaysInMonth])

    )

    ),

REMOVEFILTERS('calendar'[Date]),

    REMOVEFILTERS(BudgetSalesVol[Date]),

    USERELATIONSHIP('calendar'[MonthNumber] ,BudgetSalesVol[Month])

)

Thanks

[Anonymous]

Hi @PowerDev1977 

Try this:

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])
)

In this formula:

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.

Regards,

Nono Chen

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

[PowerDev1977]

Hi NOno,

Thank you.

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]. 

I thought when we use UseRelationship it would ignore the many to many but it doesnt.

Any clue please

Thanks