How to get Number of Days per Month for past six months in bar chart

[nurfatenmunirah]

Hi,

I'm Mun. I'm experimenting to display number of days in a month for past six months range.

For example, when I select June 2017 in the MonthYear filter, the graph will display number of days from January 2017 until June 2017.

Meanwhile, when I select May 2017, the graph should show from December 2016 to May 2017.

At the moment, it doesn't work like that. Currently, if I select June 2017, the graph will only show the value for that particular month.

For the measure, which is number of days in a month, I used this expression:

Sum(Aggr(Day(MonthEnd(Max(DateField))),DateField))

Please, kindly advise and share your idea how can I solve this.

Thank you in advance.

Cheers,

Mun

[kkkumar82]

First step take the selected date in a variable

Sum({1<Datefield = {">=AddMonths(Date($(Variable)),-6) <=Date($(Variable))"}>} Yourfield)

try this

[Anonymous · Accepted solution]

This works for me:

Chart dimension is [Month Year]

Chart expression is

count({<[Month Year]={"$(='>=' & date(AddMonths(max([Month Year]),-5),'MMM YY') & '<=' & date(max([Month Year]),'MMM YY'))"}>} distinct DateField)