Forum Discussion

NadaBen's avatar
NadaBen
Helper I
9 years ago

dynamic filter

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...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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's avatar
NadaBen
Helper I
9 years ago

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's avatar
    Anonymous
    Not applicable
    9 years ago

    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