Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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!
Hmm.. I've tried all three of these and I continue to get some dates and some dashes.
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!
Thank you Rafael! I had to play around with it but I did get it to work, appreciate your help!
Hi @lshorey
im glad you made it 👍