Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have looked around in the community for a solution to this, but can't seem to find one that matches what I need.
My issue is: When a Month is selected and Year NOT selected... I need my expression to filter a specific date field (i.e. [Bill Date]) on the Current Year AND the Month that is selected.
For example, if April is selected and no year selection is made, I need my set analysis to do the equivalent of:
[Bill Date] = {"<=$(=date('2019-04-30','MM/DD/YYYY') )>=$(=date('2019-04-01','MM/DD/YYYY'))"}
The part I can't figure out is how to do it WITHOUT any triggers, scripting, or variables... Does anyone know how to do this?
Thanks,
Ben
The easiest is you create a Month/Year Bill field in your data model, and use in the set analysis only:
{<[Month/Year Bill]={"$(=MonthName(Max([Month/Year Bill])))"}>}
Perhaps this way?
If(GetSelectedCount(Month)>0 and GetSelectedCount(Year)=0, Sum({<[Bill Date]={">=Date(MonthStart(Max([Bill Date])),'MM/DD/YYYY'))<=$(=Date(MonthEnd(Max([Bill Date])),'MM/DD/YYYY'))"}>} Measure), Sum(Measure))
I had to modify this slightly, but this actually worked for the scenario I gave!
However... now I'm struggling with the scenario where a Year is selected, but no month selection...
For example: If 2018 is selected, I want data for April 2018.
Thanks in advance!
Ben
Basically the logic is somewhat a combination of:
- if no month is selected, display data for current month
- if no year is selected, display data for current year
The part I'm struggling with now is when a Year is selected, but no Month selected... and let's just use the example where they choose 2018 as the Year, how do I filter to display data for today's month (April), but from the year 2018 (basically data for April 1st 2018 - April 30th 2018).