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

[zahlenschubser]

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]

                )

[Anonymous · Accepted solution]

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!

[Thejeswar]

@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]

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]

@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,

[zahlenschubser]

I think I found the problem - I am using the dimdate slicer with the whole month/quarter/year range and not just for a single date.

Is there any way to get around that so I can use the "higher" layers of the hierarchy as well?

[Thejeswar]

@zahlenschubser ,

If I understand you right, you want to select any level in the Date hierarchy and your logic should work for that.

In that case, you should not use SelectedValue() DAX. Instead use MIN() for your case like shown below

Measure = 
Var sd = MIN('Date'[Date])
RETURN
SUMX(FILTER('Table', 'Table'[startdate] <= sd && 'Table'[enddate] >= sd), 'Table'[quantity])

This will return the minimum date for the selection made in the hierarchy.

i.e. if Qtr2 is selected (April-May-Jun), then this is going to return 1st April as the date.

If only May is selected, this will return 1st May as the date for sd. If a single date is selected, it will return that date

Regards,

[zahlenschubser]

But shouldn't I be using max() then to grab the last date in the quarter?

Otherwise I would not be filtering for the selected quarter but for only up to the first day of it, or not?

[Akash_Varuna]

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]

Aside from the naming changes I don't see any differences to my code, what did you change?