Revenue of 1st day of each month

[sankvag7]

Hi Folks,

I want to calculate the value for 1st day of each month. I have tried various monthstart calculations in the script but having a hard time to get it. I'm posting the raw data and the output I will need.

Raw data
Date	Value
1/1/2020	5
1/2/2020	50
1/3/2020	17
2/1/2020	15
2/2/2020	30
2/3/2020	2
3/1/2020	2
3/2/2020	2
3/3/2020	2

Output
Date(MM/DD/YYYY)	Value
1/1/2020	5
2/1/2020	15
3/1/2020	2

[Quy_Nguyen]

Hi @sankvag7 ,

Try below code, it solve your requirements: get the revenue of 1st day each month and also provide MoM comparison.

Source:
Load * inline [
Date,Value
1/1/2020,5
1/2/2020,50
1/3/2020,17
2/1/2020,15
2/2/2020,30
2/3/2020,2
3/1/2020,20
3/2/2020,3
3/3/2020,6
4/2/2020,25
4/3/2020,6
4/4/2020,3
5/1/2020,0
5/2/2020,0
5/3/2020,20
5/4/2020,10
];

// Get 1st day has revenue in month
_tmp:
Load 
	MonthName(Date) As Month, 
    Min(Date)  as Date
Resident Source
Where not IsNull(Value) and Value <> 0
Group by MonthName(Date);

Left Join
	Load Date, Value
Resident Source;

Drop Table Source;

// Calculate MoM
_result:
Load 
	Month, 
    MonthStart(Month) as MonthStart,
    Date as RevenueBeginDate, 
    Value,
    Peek(Value) as LM, 
    Value - Peek(Value) as MoM
Resident _tmp
Order by Date;

Drop Table _tmp;

 this is the result:

[sankvag7]

Thanks @Quy_Nguyen . That is helpful.

How can I get maxmonth(value),prevmonth(value) MoM change, MoM% change If I want to show it in a KPI scorecard?

[Quy_Nguyen]

Are you trying to get the latest month's value and compare to previous month?

For example in the data above, we will get  data of May and compare to April? If yes, you could try this:

Current latest month: Sum({<RevenueBeginDate = {"$(=Date(Max(RevenueBeginDate)))"}>} Value)

previous month: Sum({<RevenueBeginDate = {"$(=Date(Max(RevenueBeginDate)))"}>} LM)

for the % change you just have to do the division

[Kushal_Chawda]

How you are going to represent MoM ? In KPI?

[sankvag7]

Hi @Quy_Nguyen , my old dimensions and values don't went away after I used the script you mentioned. Do I have to use load *,  in any of the temp tables?

[sankvag7]

@Kushal_Chawda 

I used the script you mentioned and get the values on a monthly level.

Now I want to be able to also show the following in a KPI:

1.  Value for  Current month start date, if March is the current month then the value on 3/1/2020 should be shown

Sum({<date = {"$(=Date(Max(date)))"}>} Value)  

2.  Value for prev month start date - which would be the value for Feb on 2/1/2020 if current month is March

Sum({<date = {"$(=Date(Max(date)-1))"}>} Value)

3. MoM would be the change from (Current month start date - Prev month start date ) which would be 

3/1/2020 - 2/1/2020

Sum({<date = {"$(=Date(Max(date)))"}>} Value) -

Sum({<date = {"$(=Date(Max(date)-1))"}>} Value)

[Kushal_Chawda]

Assuming your date format is proper then you can try below

// Current Month start

=sum({<Date={"$(=date(max({<First_Date_Flag={1}>}Date)))"}>}Value)

// Previous Month start

=sum({<Date={"$(=date(max({<First_Date_Flag={1}>}Date,2)))"}>}Value)

// MoM

sum({<Date={"$(=date(max({<First_Date_Flag={1}>}Date)))"}>}Value)-

sum({<Date={"$(=date(max({<First_Date_Flag={1}>}Date,2)))"}>}Value)

[sankvag7]

@Kushal_Chawda 

I'm getting total of all the months including null dates for the Current month KPI. 

Do you know what could be causing that?

[thi_pham]

Just contribute 1 more way, it's aggregated dimension. with your raw data, insert a table:

- Dimension: aggr(Date(min(vDate),'M-D-YYYY'), vMonth, vYear)

you might need to pre-calculate vMonth, vYear from data load: Load date#(Date, 'M/D/YYYY') as vDate, num(Month(Date),'0') as vMonth, year(Date) as vYear resident data.

- Measure: FirstSortedValue(Value,vDate)

LOL, everytime I saw your topic, I figured out another way

[Kushal_Chawda]

It will work in KPI object ..where you are using this expression?