cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

SELECTEDVALUE date not working as filter in SUMMARIZE Table

Hey folks,

How do I create a summarized table that filters the totals based on a selected value date range?

 

I have 2 tables:

Table 1 - Dates, by day for the next 365 days

Table 2 - SalesData by material, date, and qty

 

I'm trying to create a 3rd summarized table that combines inputs from Table 1 and 2 but have been unsuccessful.

 

This formula works (when I hardcode the date):

SUMMARIZE(SalesData,SalesData.Material,

  "Qty",

  CALCULATE(

    SUM(SalesData.Qty),

    FILTER(SalesData,SalesData.Date>= DATE(2018,12,1))))

 

This formula doesn't work, but I'm trying to get to work:

VAR x = SELECTEDVALUE(Dates.Day)

RETURN

SUMMARIZE(SalesData,SalesData.Material,

  "Qty",

  CALCULATE(

    SUM(SalesData.Qty),

    FILTER(SalesData,SalesData.Date>= x)))

 

I don't have a relationship setup between Tables 1 and 2 as I use the date table for multiple other tables and want to have the flexibility of not filtering on the different relationships.

 

Thanks for your help

3 REPLIES 3
Highlighted
Super User I
Super User I

@luketerry

 

You can not create dynamic calculated tables based on slicer selection

 

Calculated columns and calculated tables are evaluated once when the data is first loaded and are static until the data is refreshed or updated

 

https://docs.microsoft.com/en-us/power-bi/desktop-calculated-tables

Regards,
Zubair


Highlighted

Thanks for the feedback.

 

Do you have any recommendations for how to solve this very basic problem of trying to summarize data based on a user's selected date without having to create a relationship between the sales data and date table?

 

The reason I don't want to create a relationship is because I have other tables that I want to reference with the date table but not pre-filter the results based on the relationship.

 

This has been a very frustrating experience on my end on how confusing it's been to try and solve very basic tasks in Power BI.

Highlighted

@luketerry

 

Hi,

 

Did you try using MEASURES.

 

Just drag Qty and this MEASURE to a Table Visual

 

Measure =
VAR x =
    SELECTEDVALUE ( Dates[Day] )
RETURN
    CALCULATE ( SUM ( SalesData[Qty] ), SalesData[Date] >= x )
Regards,
Zubair


Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors