KPIs to sum current months data plus previous months data

[Gspare69]

Hello I'm looking to have 2 KPIs to sum current months data plus previous months based on a month/ year selection in a filter.

I have two columns - one with month/year eg Jan 23,called month_year and one with sales eg £2000 called sales.

I also have a filter to select a month year and I want to display the selected month's sales and the previous months sales in 2 separate kpis.

Prob very simple but in new to this sorry

[Defo]

Hi @Gspare69 ,

I hope to have understood your issue in the right way. Assuming that month_year and Sales are the only columns in the data model, a possible solution to obtain the 2 KPIs is:

• for the selected month -> 
  Sum({<month_year={"$(=MaxString(month_year))"}>}[Sales])
• for the previous month ->
  Sum({<month_year={'$(=(MaxString({<month_year=-{"$(=MaxString(month_year))"}>} month_year)))'}>}[Sales]) 
  this set analysis takes the max value of your month_year dimension after removing the actual Max month_year

I tried testing this in one of my apps and it seems to work fine.

Let me know,
Davide

[Gspare69]

Hello, thank you for your reply, I may not have made myself clear.

It is not necessarily the latest, max, month that I will need to sum.

The user will be able to select any past month and so display the chosen months and the previous months data.

[Aditya_Chitale]

@Gspare69 , try below solution

Expression for Chosen month KPI:

=Sum({<monthyear_New={'$(=max(monthyear_New))'}>}sales)

Expression for previous month KPI:

=Sum({<monthyear_New={'$(=Date(num#(max(monthyear_New)-1),'MMM-YY'))'}>}sales)

Output:

Regards,

Aditya

[Defo]

Hi @Gspare69 ,

If I am not wrong the solution I suggested should work accordingly with the selection made. The selected month_year is considered as the max month_year and the second formula picks the month_year - 1.

Give it a try, meanwhile I try to work on another solution.

Davide 😊 

[Glynn1969]

Hi - thanks for your reply but i just seem to be getting a repeat of the previous months data on my report.