How to create rolling 12 months in measure

[siddharthsoam]

Hi

I have data in the below format.

Fiscal Year	Accounting Period	Month	Rev
2018	1	April	13
2018	2	May	23
2018	3	June	34
2018	4	July	54
2018	5	August	12
2018	6	September	76
2018	7	October	45
2018	8	November	23
2018	9	December	11
2018	10	January	10
2018	11	February	3
2018	12	March	12
2019	1	April	34
2019	2	May	54
2019	3	June	23

I want to find out the rolling 12 months revenue in the set analysis. For example in the above data my revenue should be counted from July 2018 to June 2019. Thus it should come 357. Please suggest me a generalized expression.

[poojashribanger]

Hi

you can use below code

create date with preceeding load :

load *,date(date#('01'&'-'&[Accounting Period]&'-'&[Fiscal Year],'DD-MM-YYYY')) as date;

load * inline [

Fiscal Year         ,Accounting Period        ,Month, Rev

2018,    1,          April,     13

2018 ,    2  ,        May,     23

2018,   3,             June,     34

2018,    4,          July,      54

2018,    5,          August, 12

2018,    6,          September,       76

2018,    7,          October,           45

2018,    8,          November,        23

2018,    9          ,December,        11

2018,    10,        January, 10

2018,    11,        February,           3

2018,    12,        March,  12

2019,    1,          April,     34

2019,    2,          May,     54

2019,    3,          June,     23];

use below code in KPI

-->sum({<date={">=$(=yearstart(max(date),0,4))<=$(=yearend(max(date),0,4))"}>}Rev)

[siddharthsoam]

Hi Poojashri,

This is working fine in the demo app but not in the main app. Can you suggest some expression I can apply in the measure section directly. example-

Concat( distinct {1<

accounting_period={"$(= '>' & $(=max({<accounting_period=,[Fiscal Year]={'FY19'}>}accounting_period)-12) & '<=' & $(=max({<accounting_period=,[Fiscal Year]={'FY19'}>}accounting_period)))"}>}[Month Name],',')

I can equate the [month name] to this expression and get the revenue.

Your help is appreciated..:)

[siddharthsoam]

poojashribangera Also Poojashri when I select FY19 from fiscal year filter it should show me data for rolling 12 months from filter selection. So 357 should be reflected for selection on FY19 and 316 for selection on FY18.

[siddharthsoam]

Hi ,

PFA of the demo qvf and excel sheets.

Request your help in creating rolling 12 months revenue measure. It should work on my fiscal year filter as well.

stalwar1 shraddha.g Please have a look as well

[sunny_talwar]

Try this

=Sum({<MonthYear = {"$(='>=' & Date(MonthStart(Max(MonthYear), -12), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}>}Profit)

Where MonthYear is created in the script like this

LOAD

'FY18' as fisal_year,

    Date(Date#("Month Name"&2018, 'MMMYYYY'), 'MMM-YYYY') as MonthYear,

    "Customer group",

    "Customer Name",

    Profit,

    Cost,

    "Month Name",

    "accounting period"

FROM [lib://Lib/FY18_rev.xlsx]

(ooxml, embedded labels, table is Sheet1);

LOAD

'FY19' as fisal_year,

    Date(Date#("Month Name"&2019, 'MMMYYYY'), 'MMM-YYYY') as MonthYear,

//    Leaders,

    "Customer group",

    "Customer Name",

    Profit,

    Cost,

    "Month Name",

    "accounting period"

FROM [lib://Lib/FY19_rev.xlsx]

(ooxml, embedded labels, table is Sheet1);

[siddharthsoam]

Hi Sunny,

Thanks for coming  

I am starting my fiscal year from April , thus in your expression

Concat( distinct {1<MonthYear = {"$(='>' & Date(MonthStart(Max(MonthYear), -12), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}>}[Month Name],',')

is returning 9 months missing January, Feb and March. Going by your logic, month_year formed corresponding to these months will be Jan 2018,Feb 2018 and March 2018,they should not get considered while considering 12  months rolling data for July 2019.

[sunny_talwar · Accepted solution]

May be you are making a selection in the Year field? Ignore selection in year field (and other date and time related fields)

Concat(DISTINCT {1<MonthYear = {"$(='>' & Date(MonthStart(Max(MonthYear), -12), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}, Year, Month, Quarter, QuarterYear, Date>}[Month Name],',')

[poojashribanger]

what error you are getting while trying this in main app?

you can create master calender based on date i.e fiscal master calender