Calculate against date in dax

DAX DAX Calculations
1

Hi All,

I need help with the below case. what i want to achieve is, anybody who has subscribe before November then calculate the fees from Nov to the current month or any members who registered after November, calculate the subscription cost by from subscription date.

NB: The Nov date is hard coded and i have attached an excel file …column E has the calculation but need to be based on the date.

My table columns:
Subscription date, subscription amount, first name and last name.

THANKS IN ADVANCE

IF
subscription_date < 1/11/2022

THEN

one_off_amount = member_subscription.subscription_cost * no. of months from November 2022 to the current month (eg. Nov – Dec = 2 months )

ELSEIF

Subscription_date >=1/11/2022

THEN

one_off_amount = member_subscription.subscription_cost * no. of months from month(subscription_date) to the current month (eg. Nov – Dec = 2 months)
test_file.xlsx (9.4 KB)

2

Hello @alora101,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve mentioned. Below is the DAX measure alongwith the screenshot of the final results provided -

One of Payment = 
VAR _Pre_Defined_Subscription_Date_Parameter = 
DATE( 2022 , 11 , 01 )

VAR _Todays_Date = 
TODAY()

VAR _Subscription_Purchased_Before_Pre_Defined_Date =
ADDCOLUMNS(
    FILTER( Data ,
        Data[Subscription StartDate] < _Pre_Defined_Subscription_Date_Parameter ) , 
    "@One_Of_Payment" , 
    ( DATEDIFF( _Pre_Defined_Subscription_Date_Parameter , _Todays_Date , MONTH ) + 1 ) * Data[Subscription Cost] )

VAR _Subscription_Purchased_After_Pre_Defined_Date =
ADDCOLUMNS(
    FILTER( Data , 
        Data[Subscription StartDate] >= _Pre_Defined_Subscription_Date_Parameter ) , 
    "@One_Of_Payment" , 
    ( DATEDIFF( Data[Subscription StartDate] , _Todays_Date , MONTH ) + 1 ) * Data[Subscription Cost] )

VAR _Results =
SUMX(
    UNION(
        _Subscription_Purchased_Before_Pre_Defined_Date , 
        _Subscription_Purchased_After_Pre_Defined_Date ) , 
    [@One_Of_Payment] )

RETURN
_Results

Final Results
Final Results795×366 22.8 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

One of Subscription Payment - Harsh.pbix (81.9 KB)

3 Likes
3

Thanks very much. Exactly what i wanted.

Much appreciated

4

Thanks a million

5

Hello @alora101,

You’re Welcome!

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh