Cumulative passed days from smallest date

[MaxItaly]

Hello everyone.

I have a "Subscriptions" table that has "User_id" column and "subscribe_date" Column.

I have a "Master Calendar" table with the "MasterDate" linked to "subscribe_date".

What I'd like to achieve (for using it in other formulas) is a line graph that shows the "subscribe_date" in the x axis and the cumulative passed days in the y-axis.

What should be the formula like?

I did something like

CumulativeDays =
CALCULATE(DATEDIFF(MINA('Subscriptions'[subscribe_date]); MAXA('Subscriptions'[subscribe_date]); DAY);
FILTER(ALLSELECTED('Master Calendar');
'Master Calendar'[MasterDate]<= MAX('Master Calendar'[MasterDate])))

but it's clearly not correct.

The formula should be equal to the number of days passed from the first subscription.

May someone please help me?

[v-yulgu-msft · Accepted solution]

Hi @MaxItaly,

Please try this measure:

Passed days =
DATEDIFF (
    CALCULATE ( MIN ( Subscriptions[Subscription_date] ), ALL ( Subscriptions ) ),
    MAX ( Subscriptions[Subscription_date] ),
    DAY
)

Best regards,

Yuliana Gu

[MaxItaly]

Unfortunately it doesn't work:

I tried using both MasterDate and SubscribeDate as the x-axis.

It seems like it keeps taking the minimum value in the current selection and I don't understand why: the ALL() function should do the trick, isn't it?

[v-yulgu-msft]

Hi @MaxItaly,

Could you please post sample data and show us your desired output with examples?

Regards,

Yuliana Gu