Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
My chart look like this
Measures that I use :
Sum({$<[Date]={">=$(=AddYears(Date(MonthStart(Today())),0)) <=$(=AddYears(Date(Today()),0))"}>}FactSoldAmount) --- for 2018
Sum({$<[Date]={">=$(=AddYears(Date(MonthStart(Today())),-1)) <=$(=AddYears(Date(Today()),-1))"}>}FactSoldAmount) --- for 2017
Sum({$<[Date]={">=$(=AddYears(Date(MonthStart(Today())),-2)) <=$(=AddYears(Date(Today()),-2))"}>}FactSoldAmount) ----- for 2016
I want to have current month information as default but when I select the month it should show the selected month's data in these years.
I wrote this but it doesn't work
If( GetSelectedCount(Month)=0,
Sum(Sales),
Sum({$<[Date]={">=$(=AddYears(Date(MonthStart(Today())),0)) <=$(=AddYears(Date(Today()),0))"}>}Sales)
) --------- for 2018
But it shows me the whole year's data.
EDITED:
Hi,
try this:
If( GetSelectedCount(Month)=0,
Sum(Sales),
Sum({$<[Date]={">=$(=Date(MonthStart(Max(Date))))<=$(=Max(Date))"}>}Sales)
It shows me the whole year, but I want to show only the current month as default
Also, When I select month it shows me only one year, not all the 3 years .
Would you be able to attach some data (which represent your table structure) ?
Sorry, I can't
the code that you suggested for 2018, works but incorrectly .
For example, today is April and I have data in January, March, February and April. If I choose May, it shouldn't show anything
Also, other years doesn't work
Hi,
I think that you need something like,
If( GetSelectedCount(Month)<>0,
Sum(Sales),
Sum({$<[Date]={">=$(=Date(MonthStart(Max(Date))))<=$(=Max(Date))"}>}Sales)
So you are saying if a month is selected the sum(Sales) Else ....
Mark
It doesn't work. For example, if I write only this
Sum({$<[Date]={">=$(=Date(MonthStart(Max(Date))))<=$(=Max(Date))"}>}Sales)
it shows current month information, but if I select month, for example May, What does it show?
We don't have the information in May in 2018
Hi,
When you remove the if i would expext to see what you are seeing.
The $ in your set analysis means inculde any selections, so you select May your date field is only looking at dates in May.
If you always want to show May, Then maybe change the $ to 1, but that would always ignore all selections.
I personally, make flags in my script to mark current month, Current year dates to make the Set analysis quicker and easier.
Mark