Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Magpie_Rob
Helper I
Helper I

SUM when date > dynamic date

Hello, 

 

I'm trying to perform a dynamic calculation. the premis is I want to see when a customer first used a promotional code, and how much the have spent with us after. 

I have calculated the the first promotion date using the following DAX

Orders After Min Promo = 
VAR amt = SUM ( 'report v_fct_sales_order_promo_customers'[sales_order_item_total_amount])
VAR mcd = MIN ('report v_dim_sales_order_promo_codes'[CompletedDate])

RETURN 
CALCULATE( amt, mcd > 'report v_fct_sales_order_promo_customers'[date_date])

 

see screen shot below, the min date 08th december - so I would expect the DAX to calculate the 3 lines on the 13th December which total  £51.14 but its not returing the expected result it seems to be ignoring the date and just giving me the total. I have included screen shots of my model and report page with areas highlighted.

 

ModelModelReport PageReport Page

1 ACCEPTED SOLUTION
VijayP
Super User
Super User

@Magpie_Rob 

CALCULATE(amt,Filter(all(DAtedim),datesdim(date)>mcd))

You  need to created  Date Table to connect to the fact table to achieve above




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


View solution in original post

4 REPLIES 4
VijayP
Super User
Super User

@Magpie_Rob 

CALCULATE(amt,Filter(all(DAtedim),datesdim(date)>mcd))

You  need to created  Date Table to connect to the fact table to achieve above




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


@VijayP  you are a genius and a gentleman, thank you very much

Result = CALCULATE(SUM ( 'report v_fct_sales_order_promo_customers'[sales_order_item_total_amount]), FILTER(datetable,DateTable[Date] > [MCD] )  )

@Magpie_Rob You are Welcome, I Appreciated your Kudos by pressing 👍Icong. Thanks




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


amitchandak
Super User
Super User

@Magpie_Rob ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.