Forum Discussion

PowerDev1977's avatar
PowerDev1977
Regular Visitor
1 year ago

Use relationship

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 t...
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
PowerDev1977
Regular Visitor
1 year ago

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