Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello: I am trying to pull data for same month for the current and the previous years.
Current Fiscal Year and year to date data:
My Expression: Sum for Current year -
=SUM({<FY={$(vMaxFY)},Month={<=$(vPrevMonth)}>}[Volume Received])
Previous Fiscal Year and year to date data:
My Expression: Sum for Previous year - the bold part is not working. If I remove "<=" it is pulling the last month data.
=SUM({<FY={$(vPriorFY)},Month={<=$(vPrevMonth)}>}[Volume Received])
When I add the "<=" in my formula it is not working. I changed the value to Month Number from Long Name in the variable and still no luck.
Hope my question makes sense 🙂 Any help is greatly appreciated. Thank You.
Best Regards,
Archana
Oh so you have a fiscal year issue here. You need to create your MonthNum field using a fiscal calendar. To create a fiscal year calendar, look here:
Fiscal and Standard Calendar generation
One you use this calendar to generate a new MonthNum, you should be able to use that in your set analysis
Normally its a best practice in set analysis for string equals use single quotes and for Expression comparison use double quotes
Check the below expression
=SUM({<FY={'$(vPriorFY)'},Month={"<=$(vPrevMonth)"}>}[Volume Received])
Thank you Adhimulam.
I tried this ... no luck with this either .. returning zeros.
If I take out <= I am getting last months data. If I want to get the sum of all the previous months (YTD) data using <= it is not working.
Create a new field in the script like this
LOAD Num(Month(DateField)) as MonthNum,
DateField,
Month(DateField) as Month,
....
FROM ....
and then try this
=Sum({<FY={$(vPriorFY)},MonthNum={<=$(vPrevMonth)}, Month>}[Volume Received])
Make sure to adjust your vPrevMonth function so that it is using MonthNum field
Hi Archana,
I had a drought in your Exp.
=SUM({<FY={$(vPriorFY)},Month={<=$(vPrevMonth)}>}[Volume Received])
here your variable in this exp it will treate like EXP you have to put Double Cotes like below.
=SUM({<FY={'$(vPriorFY)'},Month={"<=$(vPrevMonth)"}>}[Volume Received])
Regards
Sathish
Thanks Much Sunny.
This is returning data. But, am getting sum for the entire prev Fiscal Year - not just the YTD to compare to current Fiscal years data.
Attached is my report with sample data similar to what I am using.
The FY Comparison table is where my expressions are ... when I select 2017 I get YTD for 2017 and total for FY 2016
Please help ...
THANK YOU!!
You variable is 12 when I select 2017. What do you want your variable to be when you select 2017? Today's month? January (Month 1)?
It will be December. Data will be FYTD thru last month
Example: Using the data attached Fiscal year starts in October.
If I select 2017 - the sum of volume received for FY2017 should be October, November and December and FY2016 data should be for the same period as well
THANKS A MILLION FOR YOUR TIME ON THIS...
Regards,
Archana
Oh so you have a fiscal year issue here. You need to create your MonthNum field using a fiscal calendar. To create a fiscal year calendar, look here:
Fiscal and Standard Calendar generation
One you use this calendar to generate a new MonthNum, you should be able to use that in your set analysis
Thank you very much Sunny. Will give it a try.