Pushing parameters into a pivot table

[martingaleh]

If I have a list of weeks 1,2,3,4,5,6,7,8,10

I want to be able to type a 3 in the workbook and the labels and summation in the pivot will change to
past due,4,5,6,7,8,10

And if I type a 5 in the workbook, the labels and summation in the pivot will change to
past due 6,7,8,10

Is this possible?

 

[anvg]

Hi

Is this possible?

Directly, I think no. You can create named cell where you will change values (3, 5) and create Power Query query for filtering week table by Table.SelectRows by that named cell value. Or to create independed slicer table with values (..3, 4, 5..) and filtered by its selected value, aka
CALCULCATE(expression, FILTER(weekTable, weekTable[week] > MIN(slicerTable[value])))
Regards,

 

[istiasztalos]

If I understand the original post correctly, this is not about filtering, but grouping certain elements in the pivot table. (specifically grouping oldest X week items into "past due" item)

I'd add a helper column to the data, like "modified_weeknum" where a formula would either keep the original weeknum or convert based on what number is selected to be grouped

Then in your pivot the design is based on this "modified_weeknum" instead of the original

 

[martingaleh]

If I understand the original post correctly, this is not about filtering, but grouping certain elements in the pivot table. (specifically grouping oldest X week items into "past due" item)

I'd add a helper column to the data, like "modified_weeknum" where a formula would either keep the original weeknum or convert based on what number is selected to be grouped

Then in your pivot the design is based on this "modified_weeknum" instead of the original

Yes, this is what I"m trying to do. Can I do it without a slicer?

Also, I'm trying to do a thing like this. Pretend I have 'date' dimension table<-----'fact' fact table
and fact table has a [date] and a [type] column. I want to make a new calculated date column on the 'date' that looks like if 'date'[date]<max('fact'[date] when type = "x") then x else y

Or in sql, combining the two

declare @maxdate date;
set @maxdate = select max(date) from fact where type = x

select if(date.date <@maxdate, "x","y") as newdate, date.date, type from fact left join date on fact.date = date.date

 

[istiasztalos]

Hi,

I don't think you need a slicer anywhere here.

Not sure about the other question, I'm not familiar with SQL, are you looking for the formula to replace old dates preceding a specific date with the "past due" string?

 

[istiasztalos]

I'm terribly sorry martingale, I came here from the '0 reply threads' page and didn't realize this is not an Excel question but a Power BI one

 

[anvg]

in sql, combining the two

declare @maxdate date;
set @maxdate = select max(date) from fact where type = x


select if(date.date <@maxdate, "x","y") as newdate, date.date, type from fact left join date on fact.date = date.date

No problem DAX version of it

=ADDCOLUMNS (
    ADDCOLUMNS (
        VALUES ( 'date'[date] ),
        "type", CALCULATETABLE ( VALUES ( 'fact'[type] ) )
    ),
    "newdate", IF (
        'date'[date]
            < CALCULATE (
                MAX ( 'fact'[date] ),
                FILTER ( 'fact', 'fact'[type] = x )
            ),
        "x",
        "y"
    )
)

Regards,

 

[anvg]

Sorry, I have some mistakes

[COLOR=#333333]=ADDCOLUMNS ([/COLOR]    ADDCOLUMNS (
        VALUES ( 'date'[date] ),
        "type", CALCULATETABLE ( VALUES ( 'fact'[type] ), 'fact'[type] = x_value )
    ),
    "newdate", IF (
        'date'[date]
            < CALCULATE (
                MAX ( 'fact'[date] ),
                 'fact'[type] = x_value
            ),
        "x",
        "y"
    ) [COLOR=#333333])[/COLOR]

Or

=SUMMARIZE (
    ADDCOLUMNS (
        FILTER (
            CROSSJOIN ( 'date', 'fact' ),
            'date'[date] = 'fact'[date]
        ),
        "newdate", IF (
            'date'[date]
                < CALCULATE (
                    MAX ( 'fact'[date] ),
                    'fact'[type] = x_value
                ),
            "x",
            "y"
        )
    ),
    [newdate],
    'date'[date],
    'fact'[type]
)

Regards,

 

[martingaleh]

I'll have to figure these out, they're pretty complex. I have to say, if the purpose of power pivot is to prevent analysts from having the learn sql or obiee or cognos, those three tools were much easier to learn than power pivot

 

[anvg]

I'll have to figure these out, they're pretty complex

Is it so difficult for you to upload (onedrive, google drive, dropbox) any example with what is and what you want? Or we will spend our time for speaking on fingers in next posts, or not?