SELECTEDVALUE date not working as filter in SUMMARIZE Table

[Anonymous]

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

[Zubair_Muhammad]

@Anonymous

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

[Anonymous]

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.

[Zubair_Muhammad]

@Anonymous

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 )