Limiting Date Dimension in Table

[lshorey]

Hi all, I've been scouring the boards for a while and am not coming up with the answer.  I don't have the limit function available on my date dimension and have written a couple of different expressions to be able to only display the last 13 months. 

=if(INVOICE_DT>+Addmonths(today(),-13),INVOICE_DT)

or 

=if(INVOICE_DT >=monthstart(Addmonths(today(),-13, INVOICE_DT)

Initially, it looks like it works but then, I notice that it replaces some of the dates with a dash and therefore, I have data in my table that I don't want.  

Any ideas on how to fix?  

TIA

[RafaelBarrios · Accepted solution]

Hi @lshorey 

try this

for a chart

dimension: INVOICE_DT

Measure: Sum({< INVOICE_DT={">=$(=monthstart(addmonths(today(),-13)))"} >}[Sponsored MAil])

try not to use an if statement in dimension, its not the best approach and may give you poor performance

or, if you are trying ti build a filter pane 

=if(INVOICE_DT >= monthstart(addmonths(today(),-13)),INVOICE_DT)

or, also for a filter

=aggr(only({< INVOICE_DT={">=$(=monthstart(addmonths(today(),-13)))"} >}INVOICE_DT),INVOICE_DT)

Hope this helps,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!

[lshorey]

Hmm.. I've tried all three of these and I continue to get some dates and some dashes. 

[RafaelBarrios]

ok,

your problem should be that other dimension or measure is returning more values.

so, try disabling this for you calculated dimension

be aware that dashed with grey background means null or empy 

Hope this helps,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!

[lshorey]

Thank you Rafael!  I  had to play around with it but I did get it to work, appreciate your help!  

[RafaelBarrios]

Hi @lshorey 

im glad you made it 👍