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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
zanottiluca
Helper II
Helper II

retrieve the fixed revenue from specific date but depending on product selected (based on date)

hi guys, 

I have a weird scenario that I never encountered and I am seeking some precious help. 

I am building a baseline for revenue caluclated under a specific timeframe (10/07/24-13/07/2024) and to do so I have build the following formula. 


tot revenue baseline = SUMX(FILTER(ALL('Date'[Date]),'Date'[Date]>=DATE(2023,07,10)&& 'Date'[Date]<=DATE(2024,07,13)),[Revenue])


the problem I am facing is that when the user use the date slicers it should select only some specific products existent in that date range, and the baseline above should work only for that product selected. 
So for instance if the user select the slicer 15/07 to 15/07 (when Apple and Pear were sold) it should calculate the total revenue from the 10/07/2024 till 13/07/2024 of these products only (image2).
Image 1
zanottiluca_1-1722620295223.png
image 2zanottiluca_2-1722620443363.png

 



Currently I am fixing the date in the formula and the baseline is just retrieing the total.
Note: I am using the SUMX as in next iteration I need to calculate the AVERAGEX and also the average of customers.
Any help would be massively appreciated.

Thansk in advance




1 ACCEPTED SOLUTION
rajendraongole1
Super User
Super User

Hi @zanottiluca - create below measure to dynamically filter the products based on the user's selection.

tot revenue baseline =
CALCULATE(
SUMX(
FILTER(
ALL('Date'[Date]),
'Date'[Date] >= DATE(2023, 07, 10) &&
'Date'[Date] <= DATE(2024, 07, 13)
),
[Revenue]
),
KEEPFILTERS(VALUES('Products'[Product]))
)

 

Hope it helps.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

I do not understand.  If you are selecting dates in a slicer, then why are you specifying thoe dates in the filter argumet of the CALCULATE() function.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
rajendraongole1
Super User
Super User

Hi @zanottiluca - create below measure to dynamically filter the products based on the user's selection.

tot revenue baseline =
CALCULATE(
SUMX(
FILTER(
ALL('Date'[Date]),
'Date'[Date] >= DATE(2023, 07, 10) &&
'Date'[Date] <= DATE(2024, 07, 13)
),
[Revenue]
),
KEEPFILTERS(VALUES('Products'[Product]))
)

 

Hope it helps.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





thanks @rajendraongole1  that worked indeed, quiet sure I tried KEEPFILTERS but not sure I used VALUES. 
Amazing

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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