Filter by pure month (not relative date) using measure

[JustinDoh1]

I am sharing my Pbix file here.

I am trying to apply filter on month to display data of (1) current month,  (2) 1 month prior,  (3) 2 month prior etc.

So, for example (today is 10/7/2021), if we are talking about 1 month prior, it would be 9/1 - 9/30, not relative dates like 9/6 - 10/6.

I looked online for example:

The closest that I found was using a measure like this:

(source1, source2 )

last_month_data =
var current_month= MONTH(TODAY())
       return
          CALCULATE(SUM('Table1'[Data]),
                                           FILTER(
                                                      'Table1',
                                                      MONTH('Table1'[Month]) = current_month -1
                                                   )
                                      )

On my Pbx file, date field is called "IncidentDate".

Bottom is screenshot of my visuals:

On these visuals, I am using relative date like below. but that is not what I want.

For 2 month prior, it should be 8/1/2021 - 8/31/2021.

How do I apply the logic into filter?

I guess I am stuck here:

How do I apply measure [Falls Count] into sum?

OR..

It appears that second screenshot (measure) seems to be working (I need to double check), but I am curious why I have to break down the measure "Falls Count" into this

to make it work..

Thanks.

[mahoneypat · Accepted solution]

You can just add this column to your Dates table to get the relative months from Today, and then add that column to your visual with is -1 and is -2.

Months From Today = DATEDIFF(Dates[Date], TODAY(), MONTH)

Pat

[AllisonKennedy · Accepted solution]

@JustinDoh1 

I have added my Date table - I recommend deleting your Dates table to avoid confusion of too many tables. You need to make sure to mark as date table, whichever option you choose: https://excelwithallison.blogspot.com/search?q=dimdate 

I also created a DimHour table to simplify a bit, this means you don't need to create those calculated columns in the Fact table. 

Here's what it looks like now: 

Measure pattern is:

Falls Count -1 Month =

VAR _Today = MAX(DimDate[Date])

VAR _NumberMonths = -1

var _StartDate = EOMONTH(_Today, _NumberMonths)

VAR _Result =

CALCULATE(

[Falls Count],

DATESINPERIOD(DimDate[Date], _StartDate, -1, MONTH)

)

RETURN

_Result

Note: You could get the -1 Month using the relative date filter, just choose Calendar month: 

But that won't work for 2 month prior as it will include both 1 and 2 months prior, thus the need for the DAX I gave above.

See attached updated file below signature. 😀

[JustinDoh1]

@AllisonKennedy 

I have a question. I see that you included a slicer that selects Year and Month. I am trying to make the current month as a default value (more dynamic) instead of choosing the value from this filter. How do I that? I guess I do not need this filter at all.  Thanks. 

[AllisonKennedy]

@JustinDoh1 

---

@JustinDoh1 wrote:

@AllisonKennedy 

I have a question. I see that you included a slicer that selects Year and Month. I am trying to make the current month as a default value (more dynamic) instead of choosing the value from this filter. How do I that? I guess I do not need this filter at all.  Thanks. 

---

Correct, you could use Relative Date slicer on the date column and choose this month - that will be dynamic, or use Page/All Pages level filter in the filter pane.

[mahoneypat · Accepted solution]

You can just add this column to your Dates table to get the relative months from Today, and then add that column to your visual with is -1 and is -2.

Months From Today = DATEDIFF(Dates[Date], TODAY(), MONTH)

Pat

[JustinDoh1]

@mahoneypat I think your solution totally makes sense to me. Going back to my struggle with coming up with measure, is there any solution? Thanks.

[mahoneypat]

You should be able to just use a measure expression like this.  Is that what you mean?

NewMeasure =
VAR vThisMonthIndex =
    MIN ( Date[Months From Today] )
RETURN
    CALCULATE (
        [YourMeasure],
        ALL ( Date ),
        Date[Months From Today] = vThisMonthIndex - 1
    )

Pat