cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
image 2

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.

1 ACCEPTED SOLUTION
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.

Proud to be a Super User!

3 REPLIES 3
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
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.

Proud to be a Super User!

Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors