Calculate the actual value of November, on October and other months back, with two different moving

[Las_Palmeras]

Hello everyone.

I can’t solve this problem, sorry.

I have a simple model with two tables, dates and orders. In the order table there are two dates, order_date and activation_date.

I have one relationship active with order_date and the last one is non-active (with activation_date).

In the process there are orders that are processed in the same month and others ones in subsequent months.

I would need to compare how many orders are created and activated to date (12th nov), with the orders created and activated during the same period  of time (12 th nov) in the previous month.

I am using dateadd, but the model moves just in one direction (active relationship)

Could you please help me?

Thank a lot

		desired (create and activation ad the some period than november)	My formula is doing for all the month in the filter contex, just for the actual month the result is correct
month	creation_date	Activation_date
JANUARY 23	1015	145	create date is ok, activation date take into account all the month
FEBRUARY 23	1638	234	create date is ok, activation date take into account all the month
MARCH 23	5523	789	create date is ok, activation date take into account all the month
APRIL 23	4746	678	create date is ok, activation date take into account all the month
MAY 23	6312	789	create date is ok, activation date take into account all the month
JUNE 23	2592	432	create date is ok, activation date take into account all the month
JULY 23	3024	432	create date is ok, activation date take into account all the month
AUGUST 23	198	33	create date is ok, activation date take into account all the month
SEPTEMBER 23	2820	564	create date is ok, activation date take into account all the month
OCTOBER 23	2367	789	create date is ok, activation date take into account all the month
NOVEMBER 23	2415	345	345
DECEMBER 23	0	0

[Anonymous · Accepted solution]

Hi  @Las_Palmeras ,

I created some data:

Here are the steps you can follow：

1. Create measure.

count_create =
var _today=TODAY()
var _countcurrentmonth=
COUNTX(
    FILTER(ALL('orders'),
    'orders'[Creation_date]=_today),
    [Id])
var _lastmonthtoday=
DATE(YEAR(_today),MONTH(_today)-1,DAY(_today))
var _countlastmonth=
COUNTX(
    FILTER(ALL('orders'),
    'orders'[Creation_date]=_lastmonthtoday),
    [Id])
return
IF(
    MAX('date'[Date])= _today,_countcurrentmonth - _countlastmonth,BLANK())

count_active =
var _today=TODAY()
var _countcurrentmonth=
COUNTX(
    FILTER(ALL('orders'),
    'orders'[Activation_date]=_today),
    [Id])
var _lastmonthtoday=
DATE(YEAR(_today),MONTH(_today)-1,DAY(_today))
var _countlastmonth=
COUNTX(
    FILTER(ALL('orders'),
    'orders'[Activation_date]=_lastmonthtoday),
    [Id])
return
IF(
    MAX('date'[Date])= _today,_countcurrentmonth - _countlastmonth,BLANK())

2. Result:

If it doesn't meet your expected results, you can show your expected results using images and we can help you better.

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