Dynamic selection for a month/year over previous month/year

[altawashi]

I need support in how I can write an expression to calculate
[Month on Month (MoM %): Growth comparison of Current Month Total Revenue with Previous Month Total Revenue]

the selection should be dynamic to choose any month/year over previous any month/ year.

Appreciate your support.

[stevedark]

Hi @altawashi 

I've put together a post and and an example application you can download which does prior period comparison:

https://www.quickintelligence.co.uk/prior-period-comparison/

Hopefully that will point you in the right direction.

Steve

[altawashi]

thanks @stevedark  for your support but still have not solved my question.

[stevedark]

Hi @altawashi 

If you want the user to pick two months then you simply need to pick the first and last month in the selection and apply that to set analysis, so something like this:

sum({<Month*={'$(=date(min(Month), 'MMM-YYYY'))'}>}Value)

and

sum({<Month*={'$(=date(max(Month), 'MMM-YYYY'))'}>}Value)

Growth would then be:

(sum({<Month*={'$(=date(max(Month), 'MMM-YYYY'))'}>}Value)-
sum({<Month*={'$(=date(min(Month), 'MMM-YYYY'))'}>}Value))
/ sum({<Month*={'$(=date(min(Month), 'MMM-YYYY'))'}>}Value)

If no selection is made to limit the date it will compare the very first month with the very last month. If two months are selected it will compare those. If only one month is selected then the Current and Prior will display the same and growth will be zero.

You need to ensure that the date format mask (MMM-YYYY) exactly matches the format of the month field in the data model.

Steve

[altawashi]

Thanks @stevedark again for your support,

For example, if the user selects 05.2020 & 05.2019 how the set expression will differentiate between two variables.

regarding what I thought, I think we have to sum month and year for the first parameter(selection) and store them in a variable& the same for the second one.

then we make a comparison to know which date are Max & Min.

[altawashi]

sum({$<[MonthYear Num]={'$(=max(([MonthYear Num])))'}>} [Total Revenue] )----àcurrent month

/

sum({$<[MonthYear Num]={'$(=max(([MonthYear Num])-1))'}>} [Total Revenue]---àprevious month

[altawashi]

could you please @stevedark support in explaining the concept for this expression above

[stevedark]

Hi @altawashi 

Hopefully the attached app will explain.

I've loaded some dummy data with this script:

RandomData:
LOAD
Date(AddMonths(MonthStart(today()), -RowNo()), 'MMM-YYYY') as Month,
5000 + Floor(Rand()*10000) as [Total Revenue]
AUTOGENERATE(36);

In the app you can pick two months, and it will do the difference between those.

The expressions for First Month, Latest Month and Variance are:

sum({<Month={'$(=date(min(Month), 'MMM-YYYY'))'}>}[Total Revenue])

sum({<Month={'$(=date(max(Month), 'MMM-YYYY'))'}>}[Total Revenue])

(sum({<Month={'$(=date(max(Month), 'MMM-YYYY'))'}>}[Total Revenue])-
sum({<Month={'$(=date(min(Month), 'MMM-YYYY'))'}>}[Total Revenue]))
/ sum({<Month={'$(=date(min(Month), 'MMM-YYYY'))'}>}[Total Revenue])

You can see here the outcome of this:

I'm not sure with the code you posted what you are trying to do, or what value is in the [MonthYear Num]. Taking 1 off of a month value will give you the last day of the previous month - not the start of the previous month. If it is a value, such as 202206 then removing 1 will go to the prior month, except in Janurary when it will fail.

Steve