Filters with a month and previous month view

[selracir]

I'm trying to show in a visualization the current month value and the previous month. I have some filters for year/month

I've created an example simple dataset to recreate the issue I have. The data is as follows:

YearMonth	Year	MonthName	MonthNum	Value
202001	2020	Jan	01	10
202002	2020	Feb	02	20
202003	2020	Mar	03	30
202004	2020	Apr	04	40
202005	2020	May	05	50
202006	2020	Jun	06	60
202007	2020	Jul	07	70
202008	2020	Aug	08	80

What I want to visualize is a chart with the value of the Current Month (CM) and the Previous Month (CM-1) and using the filters month/year to update accordingly. I used the following formulas:

CM = sum({$<[YearMonth]={'$(=Max(Num#(YearMonth)))'}>} Value)

CM-1 = sum({$<[YearMonth]={'$(=date(date#(Date(MakeDate(Max(Num#(Year)), right(Max(num#(YearMonth)),2), 1)-1),'MM/DD/YYYY'),'YYYYMM'))'}>} Value)

I've added below the filter of the YearMonth and the table to help to understand what is happening

When I select a specific YearMonth, for example 202003, the visualization works correctly

The problem appears when I select the month instead as it filters out the data for the previous month and shows 0 as CM-1

I don't understand why it works with YearMonth selection, but not with Month selection. I'm guessing that the problem is whit the formula I've used, but I can not figure it out how to resolve it.

Any ideas?

[sunny_talwar · Accepted solution]

First of all load YearMonth field as a date field

Date(MonthStart(Date#(YearMonth, 'YYYYMM')), 'YYYYMM') as YearMonth

and then try these expressions

CM:
Sum({$<[YearMonth] = {"$(=Date(Max(YearMonth), 'YYYYMM'))"}>} Value)

CM-1:
Sum({$<[YearMonth] = {"$(=Date(MonthStart(Max(YearMonth), -1), 'YYYYMM'))"}, MonthName, MonthNum, Year>} Value)

[selracir]

Thanks @sunny_talwar 

I've created dimension (S_YearMonth) and measures like: 

S_YearMonth: Date(MonthStart(Date#(YearMonth, 'YYYYMM')), 'YYYYMM')

S_CM = Sum({$<[S_YearMonth] = {"$(=Date(Max(S_YearMonth), 'YYYYMM'))"}>} Value)

S_CM-1 = Sum({$<[S_YearMonth] = {"$(=Date(MonthStart(Max(S_YearMonth), -1), 'YYYYMM'))"}, MonthName, MonthNum, Year>} Value)

Using these, I get the following:

I need to show only the current month and the previous month, but not all months are shown. Also it seems like it is not picking up the previous month value in S_CM-1. 

[selracir]

Just to add also that with the initial formula I could see the YearMonth in the formula

With the new formula it seems like it is blank:

[selracir]

@sunny_talwar 

I managed to get it sorted with what you sent me

S_CM = Sum({$<YearMonth= {"$(=Date(Max(Date(MonthStart(Date#(YearMonth, 'YYYYMM')), 'YYYYMM')), 'YYYYMM'))"}>} Value)

S_CM-1 = Sum({$<[YearMonth] = {"$(=Date(MonthStart(Max(Date(MonthStart(Date#(YearMonth, 'YYYYMM')), 'YYYYMM')), -1), 'YYYYMM'))"}, MonthName, MonthNum, Year>} Value)

Thanks for your guidance!!