Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to show sales and budgets by quarter year increments and I want to hide these future rows where there is a measure value for budget but not for sales obviously. How do I remove them? I was thinking something like "if yearquarter is > today, hide" or something like that.
Try this
If(
YearQuarter <= Year(Today()) * 100 + Ceil(Month(Today()) / 3)
OR NOT IsNull(Sum(Sales)),
YearQuarter
)
Hi!
If you want to filter out future data in the data load itself, you can add a condition to your WHERE clause like this "WHERE YearQuarter <= Num(Today())"
If your dataset doesn't have a direct YearQuarter numeric comparison with today's date, you might extract the year and quarter separately:
WHERE MakeDate(LEFT(YearQuarter,4), MID(YearQuarter,5,2)*3-2, 1) <= Today()
This converts YYYYQ format into a valid date and compares it.
Hi,
I would recommend to use a Set Analysis in both measure (budget and sales) to cap your measure to the period you want (max sales date or today rounded to QuarterEnd?)
Regards
Beside restricting the data-set per where-clause or the object-dimension with an if-condition you may adjust the expression with a set analysis with a reference to the date, for example:
sum({< Date = {"<=$(=today())"}>} Value)