How to calculate current vs previous month data, based on month selection

[abc_18]

Hello everyone,

In my dashboard, I have two KPI's  which shows data for current month and previous month.

I have to show the sales data based on month selection for entire month till date.

Suppose users inputs month as 'Jan' then for current month it should show 1st Jan - 21st Jan till today and in previous month KPI it should show 1st Dec - 21st Dec 2019 data.

Please suggest how can I achieve this.

Thanks

[Kashyap_R]

hi

try this

Current

Sum({<[Date]={">=$(=MonthStart(max([Date])))<=$(=Date(Max([Date])))"}>}Value)

Previous

Sum({<[Date]={">=$(=MonthStart(Addmonths(max([Date]),-1)))<=$(=(Addmonths(max([Date]),-1)))"}>}Value)

Thanks

[abc_18]

Hi kashyap,

Thanks for your quick response.

I tried to using this formula, but if I use  >=, it's just giving me '0' value, what can be the reason.

Sum({<[Date]={">=$(=MonthStart(max([Date])))<=$(=Date(Max([Date])))"}>}Value)

I have checked the data and for Jan till 19, we have data available, still it giving '0' value.

Thanks

[Kashyap_R]

Hi

If it is possible can u share the QVF or some sample, So that I can know what's going on

Also

Check what are the filters you have applied if there are any other filters like year or date try nullifying those

Check the Date format too

Thanks

[abc_18]

Hi kashyap,

I have changed the date format, now for current month data is showing, but still previous month data is showing '0'.

This is the formula I am using:-

=Sum({<sap_invoice_date={">=$(=MonthStart(addmonths(Max(sap_invoice_date),-1))) <=$(=AddMonths(Max(sap_invoice_date),-1))"}>} invoice_quantity)

I have also attached the sample test data for reference.

Regards

[Prashant_Naik]

Hi,

May be this

Sum({<sap_invoice_date={">=$(=Date(MonthStart(addmonths(Max(sap_invoice_date),-1))),'YYYY-MM-DD' )<=$(=Date(AddMonths(Max(sap_invoice_date),-1)),'YYYY-MM-DD')"}>} invoice_quantity)

Regards,

Prashant

[Kashyap_R]

Hi

Please o through the attached QVF it's working

Thanks

[Prashant_Naik]

Hi kashyap,

I am unable to find any attachment.

[Kashyap_R]

Hi

I have attached

Thanks

[abc_18]

Hi Kashyap,

Thank you very much, you just saved my day.

Can I ask for more help, I need to calculate Current YTD  vs previous YTD (Yeartodate).

for ex  if in 2020 there is only one month January with data till today , it will be compare to Jan month of 2019 from 1st Jan till 21st .

and if there are three months in 2019 it will compare to same three months of 2018.

Please suggest.

Reagrds