users select on month slicer and load previous month data only

[NISHA_S]

Hi all,

               i have to create using above data. Requirement is one month slicer will be there from jan-dec based on date column from my data.Suppose a user select dec month on slicer ,data will load only from nov1 to nov 30 data. ie, previous month data need to load. Is it possible?

[v-rzhou-msft · Accepted solution]

Hi @NISHA_S ,

As far as I know, based on Bifinity_75 's reply, the code will only return the summarize result based on SUM().

If there is an reltionship between your date table and your data table, your visual will be filtered by relationship. So you couldn't see last month date in your month. If you don't only want to show aggregation in a card visual, please change the relationship between two tables to inactive and create a measure to filter your table visual.

Measure:

Filter = 
VAR _MAXDATE = MAX('Calendar'[Date])
VAR _RANGEEND = EOMONTH(_MAXDATE,-1)
VAR _RANGESTART = EOMONTH(_MAXDATE,-2)+1
RETURN
IF(MAX('Table'[Date]) >=_RANGESTART && MAX('Table'[Date])<=_RANGEEND,1,0)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bifinity_75]

Hi @NISHA_S , 

If you don't have a full column of dates. The first thing you have to do is create one, inserting a table, and writing the function: 

Calendar = CALENDAR(min(Table1[start date]),max(Table1[start date]))

Then you have to relate the 2 columns:

And finally create the following measure:

Amount_p = CALCULATE(sum(Table1[amount]),PREVIOUSMONTH('Calendar'[Date]))

Best regards