Home Forums Power Pivot Alter date filter context with measures

This topic contains 6 replies, has 2 voices, and was last updated by  stevewingjr 3 weeks, 4 days ago.

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • #11267

    theunit
    Participant
    • Started: 2
    • Replies: 7
    • Total: 9

    Hello All – I’m interested in altering the filter applied to my Calendar[Date] column using measures. To provide some specifics:

    1. My report presents Quarter-to-date and Year-to-date information
    2. My model lets the user select, by slicer, the ‘Report Date’ (using a disconnected date table)
    3. Using Report Date, I have measures for BegOfQuarter, EndOfQuarter and BegOfYear and EndOfYear
    4. I have “base” measures for report data/metrics, ie., sum[data]
    5. The I Calculate the “base” measure, filtered by DatesBetween(BegOfQuarter, EndOfQuarter) and BegOfYear, EndOf Year)
    6. So now I have a separate measure for the QTD and YTD information for each ‘base’ reporting data column/metric
    7. Multiply this out by many measures/metrics, and there is a lot of “duplication” of measures to achieve the correct date range-ing

    What I’d like to do instead is simply use my base measures on a pivot table whose Date range is linked (filtered by my measures) to BegOfQuarter and EndOfQuarter. Then have another pivot table, a copy of the quarterly table, but assign the date range filter to BegOfYear, EndOfYear. So now I only have to write one measure per reporting metric, and then customize filters for my pivot tables; as opposed to ‘copying’ a measure for every time period. Said another way – only write one measure per metric, and then produce results for reporting periods by pivot table filter, rather than by period-specific measure.

    Hope this makes sense and someone has a clever approach here.

    To attempt a TL;DR: I want to alter pivot table filter context for a date range, where the max and min dates for the range are defined by Measures based upon a disconnected date selection table.

    #11269

    theunit
    Participant
    • Started: 2
    • Replies: 7
    • Total: 9

    Adding this reply with a scrubbed copy of the model. The refresh time on this thing is really long (5-10 mins). Please see tab ‘measures’ for another note on the goal of this post.

    #11270

    theunit
    Participant
    • Started: 2
    • Replies: 7
    • Total: 9

    …another attempt to upload with a zipped copy

    Attachments:
    You must be logged in to view attached files.
    #11326

    stevewingjr
    Participant
    • Started: 2
    • Replies: 8
    • Total: 10

    Hey there,

     

    If your goal is to reduce duplicative measures, rather than bypassing the time-intelligence of the measures you’ve built,  could you consider consolidating some of your similar data? Appending your similar Mine-specific data together (MK, RK & AH) into one table in Power Query (AKA “Get & Transform”) would cut your number of measures related to those tables down by 1/3, and the same could be said for your three similar “FC” tables.  All you’d need to add is a column to each indicating whether the data is from MK, RK, or AH and you could slice/filter that data in a Pivot table.

     

    I realize this is not quite the solution you were looking for, but might help resolve some of your pains.

    #11329

    theunit
    Participant
    • Started: 2
    • Replies: 7
    • Total: 9

    Good point – that would have been a much more efficient way to build out the measures. I should have spent a bit more time thinking about the general structure.

    It doesn’t quite scratch the itch though…

    Thanks for thinking about this.

    #11330

    stevewingjr
    Participant
    • Started: 2
    • Replies: 8
    • Total: 10

    It was worth a shot to see if consolidating might help.

    Try this – in your Calendar table, add 4 Calculated columns:

    BOP – MONTH:=STARTOFMONTH ( ‘Calendar'[Date] )

    BOP – QUARTER:=STARTOFQUARTER ( ‘Calendar'[Date] )

    BOP – Year:=STARTOFYEAR ( ‘Calendar'[Date] )

    BOP – WEEK:=LOOKUPVALUE ( ‘Calendar'[Date] , ‘Calendar'[Year] ,’Calendar'[Year] , ‘Calendar'[WeekNum] , ‘Calendar'[WeekNum], ‘Calendar'[Day Of Week Number] , 1)

     

    For your YTD Metrics, try something like what’s below on a pivot table with a date slicer:

    mTonnesMilledYTD – Sliceable: = IF (
    HASONEVALUE ( ‘Calendar'[Date] ) ,
    CALCULATE (
    [mTonnesMilled] ,
    DATESBETWEEN (
    ‘Calendar'[Date] ,
    VALUES ( ‘Calendar'[BOP – Year] ),
    VALUES ( ‘Calendar'[Date] )
    )
    ) ,
    BLANK()
    )

     

    It might be a chore to rewrite all those metrics this way, but hope it solves your problem

    #11331

    stevewingjr
    Participant
    • Started: 2
    • Replies: 8
    • Total: 10

    Scratch all that — no need for the calc columns — try this:

     

    mTonnesMilledYTD – Sliceable (2): IF (
    HASONEVALUE ( ‘Calendar'[Date] ) ,
    CALCULATE (
    [mTonnesMilled] ,
    DATESBETWEEN (
    ‘Calendar'[Date] ,
    STARTOFYEAR ( VALUES ( ‘Calendar'[Date] ) ),
    VALUES ( ‘Calendar'[Date] )
    )
    ) ,
    BLANK()
    )

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.