topic Re: Display data for previous month with Month-Year filter in DAX Commands and Tips

Display data for previous month with Month-Year filter

Anonymous — Mon, 24 Jun 2024 09:21:19 GMT

I have 2 visuals that interact with my Month-Year Filter (Format MMMM YYYY):

Date_No_Duplicate = FORMAT(DATE(YEAR([CreatedDate]), MONTH([CreatedDate]), 1), "MMMM YYYY")

The 1st visual shows the selected month's data, and the 2nd visual is supposed to show the previous month's data of the selected month in filter. Each visual uses 3 measures in Y-axis.

I also have another Month-Year Filter (called Period) for the whole dashboard so I created a measure as a filter. I have included this within an IF condition in my measures so it will check if the month selected is also selected in Period filter.

SelectedPeriod =

VAR SelectedPeriodValues = VALUES('Table'[CreatedDate])

RETURN

IF (

SELECTEDVALUE('Table'[Date_No_Duplicate]) IN SelectedPeriodValues,

)

As for my issue in the 2nd visual (previous month), I've tried converting my date filter back to date type then using it (tried other date columns) as a filter by setting the relative date to the last 1 calendar month, but it's being set to May 1 - May 31 statically so it always results to no data. I also tried to create a separate measure to be used as a filter but, no luck.

Any other solutions?

Re: Display data for previous month with Month-Year filter

Anonymous — Tue, 25 Jun 2024 02:56:59 GMT

Hi Anonymous ,

We can use a date table and create a measure to meet your requirement.

1. Create a date table based on your date, there is no relationship between two tables.

Date = ADDCOLUMNS ( CALENDAR ( "2019/7/1", "2020/6/1" ), "year", YEAR ( [Date] ), "Month_name", FORMAT ( [Date], "mmm" ), "Month_number", MONTH ( [Date] ) )

2. Create a measure like this.

Measure = VAR selected_ = SELECTEDVALUE ( 'Date'[Month_name] ) VAR Date_ = CALCULATE ( MAX ( 'Date'[Date] ), FILTER ( 'Date', 'Date'[Month_name] = selected_ ) ) RETURN IF ( ISBLANK ( selected_ ), CALCULATE ( SUM ( 'Table'[Values] ) ), CALCULATE ( SUM ( 'Table'[Values] ), FILTER ( 'Table', 'Table'[Date] <= Date_ ) ) )

3. At last we need to add a month slicer based on Date[Month_name], and the result like this.

Best Regards

Yilong Zhou

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

Re: Display data for previous month with Month-Year filter

Anonymous — Tue, 25 Jun 2024 06:13:22 GMT

Hi Anonymous,

The right graph for the selected month-year's data is working properly. Here's the measure used for the light blue one (and there are 2 other measures for the remaining percentages that work as expected):

RightGraph =

IF (

[SelectedPeriod] = 1,
DIVIDE(
300 - ([CountCondition1] + [CountCondition2]),
300,
0
),

0
)

And the SelectedPeriod measure, where CreatedDate column is used as a slicer for the whole dashboard with multiple selection (Month Year format), and Date_No_Duplicate is also a slicer with single selection (Month Year format) to filter the 2 graphs that I've provided.
SelectedPeriod =

VAR SelectedPeriodValues = VALUES('Table'[CreatedDate])
RETURN
IF (

SELECTEDVALUE('Table'[Date_No_Duplicate]) IN SelectedPeriodValues,
1,
0

)

As a solution, I tried this tutorial until 4:22 for the static filter: https://www.youtube.com/watch?v=CaFpeY1lPTI where DATESINPERIOD function was used. I used DimDate[Month Year] as a slicer and I modified the measure to display the previous month's data instead of the last 6 months:
LeftGraph =

VAR MaxDate = MAX('DimDate'[Date])
VAR PrevMonth = DATESINPERIOD('New Date'[Date], MaxDate, -1, MONTH)

RETURN

CALCULATE(

DIVIDE(300 - ([CountCondition1] + [CountCondition2]),
300,
0
),

REMOVEFILTERS(DimDate),
KEEPFILTERS(PrevMonth),
USERELATIONSHIP('DimDate'[Date], 'New Date'[Date]) -- Inactive

)

Unfortunately, it still did not work, because I checked which months are included in the graph by dragging the Month Year in the X-axis, and it returns ALL:

The right graph is affected, did the same thing with the X-axis, and it's still displaying correct values for a single month. So I guess the only problem here is either my filter or measure for the left graph to display previous month's data.

Re: Display data for previous month with Month-Year filter

Anonymous — Wed, 26 Jun 2024 02:27:03 GMT

I was able to fix it. This worked for me:

PreviousPeriod =

VAR PeriodValues=

CALCULATETABLE(VALUES('Table'[CreatedDate]), REMOVEFILTERS(DimDate))

VAR PreviousMonth= EDATE(SELECTEDVALUE('DimDate'[Month Year]), -1)

RETURN

IF (

PreviousMonth IN PeriodValues,

)
Then used it in my measures with an IF condition equals to 1. I also added

REMOVEFILTERS(DimDate) in my count measures included in the calculation.

Re: Display data for previous month with Month-Year filter

Anonymous — Wed, 26 Jun 2024 06:54:00 GMT

Hi Anonymous ,

Congratulations on finding the appropriate solution through your own efforts, and I'm sure your thinking will be able to help other users on the forum.

Best Regards

Yilong Zhou