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
Anonymous
Not applicable

DAX Function get current year sales from Clients with no sale in 3 months

Good afternoon,

 

I am struggling getting the following DAX function: I need the current selected period sales from those clients who have no sale in the last 3 months.

 

So: I have Invoices, Clients and a Calendar table, and I want to show the list of Clients + this year's total sales only of those clients with no sales (sum of sales = 0) in the last 3 months. I get to calculate these las 3 months total sales, but I can not make it work to merge it so I get the Client's names and current (from January this year to today) sales.

 

The DAX for the sum I comment: CALCULATE( [Sales (K€)]; DATESINPERIOD('CALENDAR'[Date]; MAX('CALENDAR'[Date]); -3; MONTH) )

 

Regards and thanks,

Antonio

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@Anonymous

 

In this scenario, since you need to show the list of Clients in visual, you just need to apply your "Last 3 Months Sales" measure into Visual Level Filter and make it "greater than 0". Then just create a CurrentYear Sales measure and put it into visual: 

 

CurrentYear Amount =
CALCULATE (
    SUM ( 'Invoice'[Amount] ),
    FILTER (
        ALL ( 'Calendar' ),
        YEAR ( 'Calendar'[Date] ) = YEAR ( MAX ( 'Calendar'[Date] ) )
    )
)

Regards,

View solution in original post

2 REPLIES 2
v-sihou-msft
Employee
Employee

@Anonymous

 

In this scenario, since you need to show the list of Clients in visual, you just need to apply your "Last 3 Months Sales" measure into Visual Level Filter and make it "greater than 0". Then just create a CurrentYear Sales measure and put it into visual: 

 

CurrentYear Amount =
CALCULATE (
    SUM ( 'Invoice'[Amount] ),
    FILTER (
        ALL ( 'Calendar' ),
        YEAR ( 'Calendar'[Date] ) = YEAR ( MAX ( 'Calendar'[Date] ) )
    )
)

Regards,

Anonymous
Not applicable

Thank you! Really helpful, I hadn't thought it.

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.