topic Set Analysis - Month Comparison in App Development

Set Analysis - Month Comparison

Tue, 29 Mar 2016 14:37:48 GMT

I have a line chart that I'm comparing previous years with the current year. I only want to show the past months for the current year. How do I do this?

I have variables for the current date:

LET vToday = (today(1));

LET vCurrentMonth = Month(today(1));

LET vCurrentYear = Year(today(1));

In loading data I have:

Month([SH_ShipDate]) as MonthTxt,

Date(YearStart([SH_ShipDate]), 'YYYY') as Year;

Set Analysis:

Sum({1<[Year]={$(=vCurrentYear)}, [MonthTxt]={"<$(=Num(vCurrentMonth))"}>}[CALC_Sales])

This is showing all months in the current year with zero for those months in the future.

Thanks

Re: Set Analysis - Month Comparison

sunny_talwar — Tue, 29 Mar 2016 16:31:45 GMT

Try this:

LET vToday = (Today(1));

LET vCurrentMonth = Num(Month(Today(1)));

LET vCurrentYear = Year(Today(1));

Month([SH_ShipDate]) as MonthTxt,

Num(Month([SH_ShipDate])) as MonthNum,

Year([SH_ShipDate]) as Year;

Sum({1<[Year]={$(=vCurrentYear)}, [MonthTxt]={"<$(=vCurrentMonth)"}>}[CALC_Sales])

Re: Set Analysis - Month Comparison

Tue, 29 Mar 2016 18:49:33 GMT

Thanks for the reply. I realized after I posted that the set analysis was working, but it was acting differently than I expected. I also tested your suggestions, and they work the same as what I had.

So the current year shows zero for all the months after Feb. of this year. What I was hoping is that I could just not have anything show for months 3-12 of this year. Is there a way to do this? I want the results of the prior years for each month, but only up to the last whole month of the current year.

Re: Set Analysis - Month Comparison

sunny_talwar — Tue, 29 Mar 2016 22:51:52 GMT

May be something like this:

If(MonthNum <= Num(Month(Today())), Sum({1<[Year]={$(=vCurrentYear)}, [MonthTxt]={"<$(=vCurrentMonth)"}>}[CALC_Sales]))

Re: Set Analysis - Month Comparison

Wed, 30 Mar 2016 12:48:27 GMT

Thanks Sunny. That works exactly like I want it to.