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
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