Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
@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
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,
Thank you! Really helpful, I hadn't thought it.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |