Using dimdate table to filter main table with varying "in use" time frames and quantities

I have a main table with shipped / in use items with different timeframes, monthly, quarterly, yearly and a start and end date and the corresponding quantities.

What I would like to do is use my separate dimdate table to filter my visuals, but I got stuck on how to code the measure to sum up all amounts where the date from the dimdate table - my intended filter - falls within the start to end timeframe on the lines in the main table.

I was trying to use this but that doesn't seem to work:

usecount = var sd = SELECTEDVALUE(DimDate[Date])

RETURN sumx(filter(kontext

, kontext[start] <= sd

&& kontext[end] >= sd

)

, kontext[quantity]

)

Need Help

Anonymous
1 year ago
Hi zahlenschubser ,

Thanks for the reply from Thejeswar / Akash_Varuna .

You want to filter the entire selected time period (e.g., month, quarter, year), right? If so you will need to adjust the metric to take into account the entire date range within those periods, you should use both MIN and MAX to capture the entire range.

Modify the measure syntax as follows:

UseCount = VAR SelectedStartDate = MIN('DimDate'[Date]) VAR SelectedEndDate = MAX('DimDate'[Date]) RETURN SUMX( FILTER( 'kontext', 'kontext'[startdate] <= SelectedEndDate && 'kontext'[enddate] >= SelectedStartDate ), 'kontext'[quantity] )

Here's my test data:

The final page visualization is shown below:

If my understanding is wrong, please describe more clearly (in screenshot or table form) the expected result you want.

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Using dimdate table to filter main table with varying in use time frames and quantities.pbix65 KB

9 Replies

Akash_Varuna
Super User
1 year ago
Hi zahlenschubser , I think it is not working because it was not iterating properly through could you try this please
UseCount =
VAR SelectedDate = SELECTEDVALUE(DimDate[Date])
RETURN
SUMX(
FILTER(
MainTable,
MainTable[StartDate] <= SelectedDate &&
MainTable[EndDate] >= SelectedDate
),
MainTable[Quantity]
)
If this post helped please do give a kudos and accept this as a solution
Thanks In Advance
- zahlenschubser
  Helper IV
  1 year ago
  Aside from the naming changes I don't see any differences to my code, what did you change?
Thejeswar
Super User
1 year ago
zahlenschubser ,

I tried recreating your logic and I don't see any issue with it. The Below is the screenshot of the logic implementation with some sample data. Few things you can check are if your start and end columns are date data type. The DimDate doesn't have to be connected to any column in your main table as this could create interaction issues when using this logic

What exactly are you trying to do here?

Regards,
- zahlenschubser
  Helper IV
  1 year ago
  I wanted to use the dimdate hierarchy to do columns in the visual, and there it breaks somehow.
  
  The start and end columns are set to date, so that shouldn't be a problem, and the dimdate table is disconnected from the main table.
  - Thejeswar
    Super User
    1 year ago
    zahlenschubser ,
    
    If you add the Date Hierarchy column from the DimDate, since your slicer is filtered to a single date, the visual also shows that one date which is filtered. So you would not be able to really know the start and end dates associated with the quantity. Refer the screenshot below
    
    The First table has the DimDate column along with the start and end date columns, while the bottom table has only the Date Column from DimDate. As you can see, having the DateHierarchy Column from DimDate may not make sense although the numbers displayed as matching and correct
    
    Regards,
Anonymous
Not applicable
1 year ago
Hi zahlenschubser ,

Thanks for the reply from Thejeswar / Akash_Varuna .

You want to filter the entire selected time period (e.g., month, quarter, year), right? If so you will need to adjust the metric to take into account the entire date range within those periods, you should use both MIN and MAX to capture the entire range.

Modify the measure syntax as follows:

UseCount = VAR SelectedStartDate = MIN('DimDate'[Date]) VAR SelectedEndDate = MAX('DimDate'[Date]) RETURN SUMX( FILTER( 'kontext', 'kontext'[startdate] <= SelectedEndDate && 'kontext'[enddate] >= SelectedStartDate ), 'kontext'[quantity] )

Here's my test data:

The final page visualization is shown below:

If my understanding is wrong, please describe more clearly (in screenshot or table form) the expected result you want.

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Using dimdate table to filter main table with varying in use time frames and quantities.pbix65 KB

Forum Discussion

Using dimdate table to filter main table with varying "in use" time frames and quantities