DAX-mom revenue calculation by marketing campaign

[Fangyi890]

I need help solving or finding te month on month revenue of a marketing campaign for 2 years. Please help. All other solutions don't work for me. Can it be calculated by court as well? 

Thanks in advance.

id	date	user_id	session_id	booking_id	place	hours	revenue	price (one hour)
4679	2019-12-31 00:00:00	8120380520	13482	spb213482	court2	2	2556.455	877
4678	2019-12-31 00:00:00	8120380579	5633	spb65633	court6	1	1307.775	987
4677	2019-12-31 00:00:00	8120381669	12638	spb812638	court8	1	1239.0075	1039
4676	2019-12-31 00:00:00	8120381699	7224	spb67224	court6	3	4315.6575	987
4675	2019-12-31 00:00:00	8120378823	19578	spb419578	court4	1	1265.375	955
4674	2019-12-31 00:00:00	8120380592	15417	spb915417	court9	1	1113.53	764
4673	2019-12-31 00:00:00	8120380976	3375	spb43375	court4	4	4555.35	955
4672	2019-12-30 00:00:00	8120381807	11234	spb611234	court6	1	1307.775	987
4671	2019-12-30 00:00:00	8120377333	16096	spb616096	court6	1	1176.9975	987
4670	2019-12-30 00:00:00	8120376827	423	spb3423	court3	1	1552.2375	1065
4669	2019-12-30 00:00:00	8120381441	5585	spb45585	court4	1	1138.8375	955

[Anonymous · Accepted solution]

Hi @Fangyi890 ,

Thanks lbendlin  for the quick reply. I have a bit of other ideas to add:

(1) Create a new calculated column.

YearMonth = FORMAT([date], "YYYY-MM")

(2) Create a measure.

MonthlyRevenue = 
CALCULATE(
    SUM('Table'[revenue]),
    FILTER(ALLEXCEPT('Table','Table'[YearMonth]),YEAR('Table'[date])<=YEAR(TODAY()) &&
    YEAR('Table'[date])>=YEAR(TODAY())-2
))

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[lbendlin]

Your sample data is insufficient. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.