dynamic filter

[NadaBen]

Hello,

I import this table below from BI (Analysis services), this table is huge and i cannot import it entirely, in addition i need restrictred information from it, i want to filter my "snapshot day" to be the recent day available, and the commonperiod.week to get only the 5 first weeks, knowing that when  i try to change the data type for commonperiod to date i have an error because the format "week 19, 2017" is not recognised by PowerBI.

Thanks,

[Anonymous]

Hi @NadaBen,

You can try to add dynamic date filter in power query:

PowerQuery Dynamic Date Dimension Table Filtering

Regards,

Xiaoxin Sheng

[NadaBen]

Hi @Anonymous,

I read the article and followed the steps, but i don't seem to get the result. Actually, once i create (lastdate) and came back to the dable where i have the column table to do filter on it (date filter equals ), i got a error before changing "lastdate" in the M language.

What do you think is the problem?

[Anonymous]

Hi @NadaBen,

Can you please share sample file to test?

Regards,

Xiaoxin Sheng

[NadaBen]

Here is a sample of my table, so what i need is to keep the last "Snapshot day", then  12 first weeks of "Common period Week"

https://drive.google.com/open?id=0BwbSVJQB7uVJMmxZQTJIWjQ4aUk

Thanks

[Phil_Seamark]

Does your cube have a column that's DateTime/Date, or one that can be easily parsed to be DateTime/Date ?

Are you able to add columns to tabes in your cube?

[NadaBen]

Unfortunately no !

[Phil_Seamark]

Are you importing or doing a live connect?

[NadaBen]

I'm importing tables

[Phil_Seamark]

You can write MDX against both MD and Tabular cubes.  With MDX it's possible to create dynamic date ranges using a combination of VBA and strtomember functions but this is medium to advanced MDX and definitely requires knowledge of your model.

[NadaBen]

hi @Phil_Seamark

That's the part of MDX query which i want to change to be dynamic

ON COLUMNS  FROM (SELECT ({[Snapshot].[Year - Week - Date].[Day].&[2017-06-05T00:00:00]},{[Common Period].[Year - Week - Date].[Year].&[2017-01-01T00:00:00]})

I want to always get the last day of  [Snapshot].[Year - Week - Date].[Day]

and the 12 first weeks of [Common Period].[Year - Week - Date].[Week]

Can you help me please ???

[Anonymous]

Hi @NadaBen,

You can try to use below formula to get the lastdata and week of month, then use these variable to filter.

Parameters:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Week of Month", each Date.WeekOfMonth([Snapshot.Day]))

    LastDate = List.Max(#"Added Custom"[Snapshot.Day])

    Today=Date.From(DateTime.LocalNow())

Filter formula:

Custom=  Table.SelectRows(#"Added Custom", each [Snapshot.Day] >= LastDate and [Week of Month] =1)

Reference link:

Date.WeekOfMonth

Regards,

Xiaoxin Sheng