Monthly cost with monthly, quarterly and yearly limits

[IsseBisse]

I have two tables:

Issues

Customer	Date	Cost
A	2022-04-01	100
A	2022-05-04	150
B	2022-06-01	200
...	...	...

Limits

Customer	Monthly	Quarerly	Yearly
A		150
B	100		500
...	...	...	...

I want to visualize costs per customer per month but capped by all limits. Something like this:

Customer	Year-Month	Cost	<comment>
A	2022-04	100
A	2022-05	50	Because of quarterly limit
A	2022-06	0	Regardless of actual cost, because quarterly limit is reached
...	...		...
B	2022-06	100	Because of monthly limit
...	...		...

I've tried using a calculated table grouped by Customer and Year-Month, but I can't get the quarterly and yearly limits to work. Should I use a different approach (I saw someone suggest calculating daily costs then using measures)? What is the best approach?

[v-yangliu-msft]

Hi  @IsseBisse ,

Here are the steps you can follow：

1. Create calculated table.

Cost =
var _table1=
DISTINCT('Issues'[Customer])
var _table2=
DISTINCT('Issues'[Date])
return
CROSSJOIN(_table1,_table2)

2. Create calculated column.

Issue =
SUMX(
    FILTER(ALL(Issues),   'Issues'[Customer]=EARLIER('Cost'[Customer])&&YEAR('Issues'[Date])=YEAR(EARLIER('Cost'[Date]))&&MONTH('Issues'[Date])=MONTH(EARLIER('Cost'[Date]))),
    [Cost])

Rank =
RANKX(FILTER(ALL('Cost'),
'Cost'[Customer]=EARLIER('Cost'[Customer])),[Date],,ASC)

Column 2 =
var _Monthly=
SUMX(
    FILTER(ALL(Limits),'Limits'[Customer]=EARLIER('Cost'[Customer])),[Monthly])
var _Quarely =
SUMX(
    FILTER(ALL(Limits),'Limits'[Customer]=EARLIER('Cost'[Customer])),[Quarerly])
var _Yearly =
SUMX(
    FILTER(ALL(Limits),'Limits'[Customer]=EARLIER('Cost'[Customer])),[Yearly])
var _if=
IF(
    _Monthly<>BLANK() &&[Issue]>_Monthly,
    _Monthly,
    IF(
        _Quarely<>BLANK()&&[Issue]>=_Quarely,
        [Issue]-SUMX(FILTER(ALL('Cost'),'Cost'[Customer]=EARLIER('Cost'[Customer])&&'Cost'[Rank]=EARLIER('Cost'[Rank])-1),[Issue]),
        IF(
            _Yearly<>BLANK()&&[Issue]>_Yearly,
            _Yearly,[Issue]
)))
return
IF(
    _if=BLANK(),0,_if)

3. Result:

Best Regards,

Liu Yang

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

[IsseBisse]

This doesn't seem to be working correctly. For instance with a slightly large example:
Issues

Customer	Date	Cost
C	2022-01-01	100
C	2022-02-02	150
C	2022-03-03	100
C	2022-04-04	100
C	2022-05-05	150
C	2022-06-06	100
C	2022-07-08	150
C	2022-08-22	100
C	2022-09-10	150
C	2022-10-11	200
C	2022-11-01	100
C	2022-12-24	150

Limits

Customer	Monthly	Quarterly	Yearly
C	100	250	600

I would expect the following

Results

Customer	Year-month	Cost	Comment
C	2022-01	100
C	2022-02	100	Because of monthly
C	2022-03	50	Because of quarterly
C	2022-04	100
C	2022-05	100	Because of monthly
C	2022-06	50	Because of quarterly
C	2022-07	100	Because of monthly
C	2022-08	0	Because of yearly
C	2022-09	0	Because of yearly
	...	...

But instead I get
Cost

CustomerDateSum of Column 2

C	2022-01-01 00:00	100
C	2022-02-02 00:00	100
C	2022-03-03 00:00	100
C	2022-04-01 00:00	100
C	2022-04-04 00:00	100
C	2022-05-04 00:00	100
C	2022-05-05 00:00	100
C	2022-06-01 00:00	100
C	2022-06-06 00:00	100
C	2022-07-08 00:00	100
C	2022-08-22 00:00	100
C	2022-09-10 00:00	100
C	2022-10-11 00:00	100
C	2022-11-01 00:00	100
C	2022-12-24 00:00	100