Forum Discussion

zahlenschubser's avatar
zahlenschubser
Icon for Helper IV rankHelper IV
1 year ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    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!

Thejeswar's avatar
Thejeswar
Icon for Super User rankSuper User
1 year ago

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's avatar
zahlenschubser
Icon for Helper IV rankHelper IV
1 year ago

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?