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 III
Super User III

Re: SELECTEDVALUE date not working as filter in SUMMARIZE Table

@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

Try my new Power BI game Cross the River
Highlighted
Advocate II
Advocate II

Re: SELECTEDVALUE date not working as filter in SUMMARIZE Table

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
Super User III
Super User III

Re: SELECTEDVALUE date not working as filter in SUMMARIZE Table

@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 )
Try my new Power BI game Cross the River

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors